﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Diagnostics;
using Mono.Security;
using Npgsql;
using NpgsqlTypes;

namespace DAL
{
    public class AccessData
    {
        public const string Msg = "NIKSOFT";
        private string sql = "", owner = "clinic", password = "links1920", userid = "qlpk", database = "QuanLyPhongKham", m_hotenkdau = "";
        private int iRownum = 1;
        private NpgsqlConnection con;
        private NpgsqlCommand cmd;
        private NpgsqlDataAdapter ada;
        private DataSet ds = null;
        private string sComputer = null;
        string sConn = "Server=localhost;Port=5432;User Id=clinic;Password=links1920;Database=QuanLyPhongKham;Encoding=UNICODE;Pooling=true;";

        public AccessData()
        {
            if (Maincode("User") != "") userid = Maincode("User");
            if (Maincode("UserID") != "") owner = Maincode("UserID");
            if (Maincode("Password") != "") password = Maincode("Password");
            if (Maincode("Database") != "") database = Maincode("Database");
            sComputer = System.Environment.MachineName.Trim().ToUpper();
            sConn = "Server=" + Maincode("Ip") + ";Port=" + Maincode("Post") + ";User Id=" + owner + ";Password=" + password + ";Database=" + database + ";Encoding=UNICODE;Pooling=true;";
            sComputer = System.Environment.MachineName.ToString().ToUpper();
            upd_dmcomputer(sComputer);
            ds = get_data("select id,computer from " + userid + ".dmcomputer");
            DataRow r = getrowbyid(ds.Tables[0], "computer='" + sComputer + "'");
            if (r != null) iRownum = int.Parse(r["id"].ToString());
        }

        public string Maincode(string sql)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load("..\\..\\..\\xml\\maincode.xml");
            XmlNodeList nodeLst = doc.GetElementsByTagName(sql);
            return nodeLst.Item(0).InnerText;
        }
        public string get_thongso(string sql,string file)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load("..\\..\\..\\xml\\"+file+".xml");
            XmlNodeList nodeLst = doc.GetElementsByTagName(sql);
            return nodeLst.Item(0).InnerText;
        }
        public string user
        {
            get { return userid; }
        }
        private int get_id_dmcomputer
        {
            get
            {
                ds = get_data("select max(id) as id from " + user + ".dmcomputer");
                if (ds.Tables[0].Rows[0]["id"].ToString() == "") return 1;
                else return int.Parse(ds.Tables[0].Rows[0]["id"].ToString()) + 1;
            }
        }
        //----------------********CAPID*********----------------
        public int getRandom()
        {
            Random r = new Random();
            return r.Next(999);
        }

        public string getyymmddhhmiss()
        {
            return get_data("select to_char(now(),'yymmddhh24miss')").Tables[0].Rows[0][0].ToString();
        }
        //1 : d_dmbd, 2 : d_dmnhom, 3 : d_dmhang, 4 : d_dmnuoc, 5 : d_dmcongty
        //6 : d_dmhoatchat, 7 : d_dmcachdung, 8 : d_dmdvt, 9 : d_dmquycach
        //10 : dmbs, 11 : mapk, 12 : doituong, 13 :pttt, 14 :v_chidinh, 15 :theodoisua, 16 : d_dmkho, 17 : d_dmnguon, 18 : dlogin, 20 : giavp, 21: viettat
        public long get_capid_viettat
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3, '0') + get_capid_dm(21, sComputer).ToString());
            }
        }
        public long get_capid_giavp
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3, '0') + get_capid_dm(20, sComputer).ToString().PadLeft(3,'0'));
            }
        }
        public long get_capid_dmbd
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3, '0') + get_capid_dm(1, sComputer).ToString());
            }
        }
        public long get_capid_dmnhom
        {
            get
            {
                return long.Parse(get_capid_dm(2, sComputer).ToString());
            }
        }
        public long get_capid_dmhang
        {
            get
            {
                return long.Parse(get_capid_dm(3, sComputer).ToString());
            }
        }
        public long get_capid_dmnuoc
        {
            get
            {
                return long.Parse(get_capid_dm(4, sComputer).ToString());
            }
        }
        public long get_capid_dmcongty
        {
            get
            {
                return long.Parse(get_capid_dm(5, sComputer).ToString());
            }
        }
        public long get_capid_dmhoatchat
        {
            get
            {
                return long.Parse(get_capid_dm(6, sComputer).ToString());
            }
        }
        public long get_capid_dmcachdung
        {
            get
            {
                return long.Parse(get_capid_dm(7, sComputer).ToString());
            }
        }
        public long get_capid_dmdvt
        {
            get
            {
                return long.Parse(get_capid_dm(8, sComputer).ToString());
            }
        }
        public long get_capid_dmquycach
        {
            get
            {
                return long.Parse(get_capid_dm(9, sComputer).ToString());
            }
        }
        public long get_capid_mabs
        {
            get
            {
                return long.Parse(get_capid_dm(10, sComputer).ToString());
            }
        }
        public long get_capid_mapk
        {
            get
            {
                return long.Parse(get_capid_dm(11, sComputer).ToString());
            }
        }
        public long get_capid_doituong
        {
            get
            {
                return long.Parse(get_capid_dm(12, sComputer).ToString());
            }
        }
        public long get_capid_dmkho
        {
            get
            {
                return long.Parse(get_capid_dm(16, sComputer).ToString());
            }
        }
        public long get_capid_dmnguon
        {
            get
            {
                return long.Parse(get_capid_dm(17, sComputer).ToString());
            }
        }
        public int get_capid_dlogin
        {
            get
            {
                return int.Parse(get_capid_dm(18, sComputer).ToString());
            }
        }
        public long get_capid_pttt
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3,'0')+get_capid_may(13, sComputer).ToString());
            }
        }
        public long get_capid_chidinh
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3,'0') + get_capid_may(14, sComputer).ToString());
            }
        }
        public long get_capid_theodoisua
        {
            get
            {
                return long.Parse(iRownum.ToString().PadLeft(3, '0') + get_capid_may(15, sComputer).ToString());
            }
        }
        //
        private long get_capid_may(int m_ma, string m_computer)
        {
            sql = "update " + user + ".capid set id=id+1 where ma=:m_ma and computer=:m_computer";
            con = new NpgsqlConnection(sConn);
            con.Open();
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
            cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
            int irec = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (irec == 0)
            {
                sql = "insert into " + user + ".capid(ma,id,computer) values (:m_ma,1,:m_computer)";
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            sql = "select id from " + user + ".capid where ma=" + m_ma + " and computer='" + m_computer + "'";
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            ada = new NpgsqlDataAdapter(cmd);
            ds = new DataSet();
            ada.Fill(ds);
            cmd.Dispose();
            con.Close(); con.Dispose();
            return long.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        private long get_capid_dm(int m_ma, string m_computer)
        {
            sql = "update " + user + ".capid set id=id+1,computer=:m_computer where ma=:m_ma";
            con = new NpgsqlConnection(sConn);
            con.Open();
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
            cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
            int irec = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (irec == 0)
            {
                sql = "insert into " + user + ".capid(ma,id,computer) values (:m_ma,1,:m_computer)";
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            sql = "select id from " + user + ".capid where ma=" + m_ma + "";
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            ada = new NpgsqlDataAdapter(cmd);
            ds = new DataSet();
            ada.Fill(ds);
            cmd.Dispose();
            con.Close(); con.Dispose();
            return long.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        public long get_capid
        {
            get
            {
                return long.Parse(getyymmddhhmiss() + getRandom().ToString().PadLeft(3, '0') + iRownum.ToString().PadLeft(3, '0'));
            }
        }
        //End Capid

        public long get_maql_ngay_gio_vv(string m_mabn, string m_ngaygio)
        {
            ds = get_data("select maql from " + user + ".benhanpk where mabn='" + m_mabn + "' and to_char(ngay,'dd/mm/yyyy hh24:mi')='" + m_ngaygio + "' order by ngay desc");
            long l_maql = (ds.Tables[0].Rows.Count == 0) ? 0 : long.Parse(ds.Tables[0].Rows[0][0].ToString());
            return l_maql;
        }
        public long get_maql_ngayvv(string m_mabn, string m_ngay)
        {
            ds = get_data("select maql from " + user + ".benhanpk where mabn='" + m_mabn + "' and to_char(ngay,'dd/mm/yyyy')='" + m_ngay + "' order by ngay desc");
            long l_maql = (ds.Tables[0].Rows.Count == 0) ? 0 : long.Parse(ds.Tables[0].Rows[0][0].ToString());
            return l_maql;
        }
        public long get_maql
        {
            get 
            {
                return get_capid;
            }
        }
        public string get_makp(long m_maql)
        {
            ds = get_data("select makp from " + user + ".benhanpk where maql=" + m_maql);
            return (ds.Tables[0].Rows.Count == 0) ? "" : ds.Tables[0].Rows[0][0].ToString();
        }
        public int get_lanthu_vv(string m_mabn)
        {
            ds = get_data("select count(*) +1 as lanthu from " + user + ".benhanpk where mabn='" + m_mabn + "'");
            return (ds.Tables[0].Rows.Count == 0) ? 1 : int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        public int get_lanthu_kham(string m_mabn,string table)
        {
            ds = get_data("select count(*) +1 as lanthu from " + user + "."+table+" where mabn='" + m_mabn + "'");
            return (ds.Tables[0].Rows.Count == 0) ? 1 : int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        public DataSet get_data(string sql)
        {
            try
            {
                ds = new DataSet();
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                ada = new NpgsqlDataAdapter(cmd);
                ada.Fill(ds);
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message.ToString().Trim(), sComputer, "?");
            }
            finally
            {
                cmd.Dispose();
                con.Dispose();
            }
            return ds;
        }

        public void execute_data(string sql)
        {
            try
            {
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message.ToString().Trim(), sComputer, "?");
            }
            finally
            {
                cmd.Dispose();
                con.Dispose();
            }
        }

        public void upd_error(string m_message, string m_computer, string m_table)
        {
            if (con != null)
            {
                con.Close(); con.Dispose();
            }
            sql = "insert into " + user + ".error(message,computer,tables) values (:m_message,:m_computer,:m_table)";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_message", NpgsqlDbType.Text).Value = m_message;
                cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
                cmd.Parameters.Add("m_table", NpgsqlDbType.Varchar, 20).Value = m_table;
                cmd.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public void upd_dmcomputer(string m_computer)
        {
            sql = "update " + user + ".dmcomputer set computer=:m_computer where computer=:m_computer";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar, 20).Value = m_computer;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close(); con.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dmcomputer(id,computer) values (" + get_id_dmcomputer + ",'" + m_computer + "')";
                    execute_data(sql);
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "dmcomputer");
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public DataRow getrowbyid(DataTable dt, string exp)
        {
            try
            {
                DataRow[] r = dt.Select(exp);
                return r[0];
            }
            catch { return null; }
        }
        public void delrec(DataTable dt, string exp)
        {
            try
            {
                DataRow[] r = dt.Select(exp);
                for (int i = 0; i < r.Length; i++) r[i].Delete();
            }
            catch { }
        }
        public string mmyy(string ngay)
        {
            return ngay.Substring(3, 2) + ngay.Substring(8, 2);
        }
        public void writeXml(string tenfile, string cot, string s)
        {
            DataSet dst = new DataSet();
            if (System.IO.File.Exists("..\\..\\..\\xml\\" + tenfile + ".xml"))
                dst.ReadXml("..\\..\\..\\xml\\" + tenfile + ".xml");
            else dst = get_data("select 0 as stt");
            try
            {
                dst.Tables[0].Rows[0][cot] = s;
            }
            catch
            {
                DataColumn dc = new DataColumn();
                dc.ColumnName = cot;
                dc.DataType = Type.GetType("System.String");
                dst.Tables[0].Columns.Add(dc);
                dst.Tables[0].Rows[0][cot] = s;
            }
            dst.WriteXml("..\\..\\..\\xml\\" + tenfile + ".xml");
        }
        public string ReadXml(DataSet dst, string cot)
        {
            string ret = "";
            try
            {
                ret = dst.Tables[0].Rows[0][cot].ToString();
            }
            catch { }
            return ret;
        }
        public string ngayhienhanh_server
        {
            get
            {
                return get_data("select to_char(now(),'dd/mm/yyyy')").Tables[0].Rows[0][0].ToString();
            }
        }
        public string ngay_update_server
        {
            get
            {
                return get_data("select to_char(now(),'dd/mm/yyyy hh:mi')").Tables[0].Rows[0][0].ToString();
            }
        }
        public string giohienhanh_server
        {
            get
            {
                return get_data("select to_char(now(),'hh:mi')").Tables[0].Rows[0][0].ToString();
            }
        }
        public string Hoten_khongdau(string s)
        {
            ds = new DataSet();
            ds.ReadXml("..\\..\\..\\xml\\khongdau.xml");
            string s1 = s.Trim().ToUpper(), s2 = "";
            DataRow r;
            for (int i = 0; i < s1.Length; i++)
            {
                if (s1[i].ToString().Trim() != "")
                {
                    r = getrowbyid(ds.Tables[0], "codau='" + s1[i].ToString() + "'");
                    if (r != null) s2 += r[1].ToString();
                    else s2 += s1[i];
                }
            }
            return s2;
        }
        public System.DateTime StringToDateTime(string s)
        {
            string[] format1 ={ "dd/MM/yyyy" }, format2 ={ "dd/MM/yyyy HH:mm" };
            return System.DateTime.ParseExact(s.ToString(), (s.Length == 10) ? format1 : format2, System.Globalization.DateTimeFormatInfo.CurrentInfo, System.Globalization.DateTimeStyles.None);
        }

        public string DateToString(string format, System.DateTime date)
        {
            if (date.Equals(null)) return "";
            else return date.ToString(format, System.Globalization.DateTimeFormatInfo.CurrentInfo);
        }

        public bool bNgay(string ngay)
        {
            try
            {
                if (ngay.IndexOf("_") != -1 || (ngay.IndexOf(" ") != -1 && ngay.Trim().Length == 10)) return false;
                int len = ngay.Length;
                if (len == 0) return false;
                string dd = ngay.Substring(0, 2), mm = ngay.Substring(3, 2), yyyy = ngay.Substring(6, 4);
                string s31 = "01+03+05+07+08+10+12+", s30 = "04+06+09+11+", s2829 = (int.Parse(yyyy) % 4 == 0) ? "29" : "28";
                if (int.Parse(yyyy.Substring(0, 1)) < 1) return false;
                if (int.Parse(mm) < 1 || int.Parse(mm) > 12) return false;
                if (s31.IndexOf(mm + "+") > -1)
                {
                    if (int.Parse(dd) < 1 || int.Parse(dd) > 31) return false;
                }
                else if (s30.IndexOf(mm + "+") > -1)
                {
                    if (int.Parse(dd) < 1 || int.Parse(dd) > 30) return false;
                }
                else if (int.Parse(dd) < 1 || int.Parse(dd) > int.Parse(s2829)) return false;
                if (len > 10)
                {
                    string hh = ngay.Substring(11, 2), MM = ngay.Substring(14, 2);
                    if (int.Parse(hh) > 23) return false;
                    if (int.Parse(MM) > 59) return false;
                }
                return true;
            }
            catch { return false; };
        }
        public bool bGio(string gio)
        {
            if (gio.IndexOf("::") != -1) return false;
            else
            {
                string hh = gio.Substring(0, 2), MM = gio.Substring(3, 2);
                if (int.Parse(hh) > 23) return false;
                if (int.Parse(MM) > 59) return false;
                return true;
            }
        }
        public string ChangeCase(string s)
        {
            string _st = "";
            if (s != "" && s.Length > 1)
            {
                string[] val = s.Replace("  ", " ").Split(' ');
                for (int i = 0; i < val.Length; i++)
                {
                    _st += val[i].ToString().Substring(0, 1).ToUpper() + val[i].ToString().Substring(1).ToLower() + " ";
                }
            }
            else _st = s.ToUpper();
            return _st = _st.Trim(' ');
        }
        public bool bNgay(string ngay1, string ngay2)
        {
            int d1 = DateTime.Now.Day;
            int m1 = DateTime.Now.Month;
            int y1 = DateTime.Now.Year;
            if (ngay1 != "")
            {
                y1 = int.Parse(ngay1.Substring(6, 4));
                m1 = int.Parse(ngay1.Substring(3, 2));
                d1 = int.Parse(ngay1.Substring(0, 2));
            }
            int d2 = int.Parse(ngay2.Substring(0, 2));
            int m2 = int.Parse(ngay2.Substring(3, 2));
            int y2 = int.Parse(ngay2.Substring(6, 4));

            if (y2 > y1) return false;
            else if (y2 < y1) return true;
            if (m2 > m1) return false;
            else if (m2 < m1) return true;
            if (d2 > d1) return false;
            return true;
        }
        public string Tuoivao(string ngayvao, string ngaysinh)
        {
            string tuoi = "";
            int namht = DateTime.Now.Year;
            int thanght = DateTime.Now.Month;
            int ngayht = DateTime.Now.Day;
            int gioht = DateTime.Now.Hour;

            int nam = int.Parse(ngaysinh.Substring(6, 4));
            int thang = int.Parse(ngaysinh.Substring(3, 2));
            int ngay = int.Parse(ngaysinh.Substring(0, 2));
            int gio = (ngaysinh.Length > 10) ? int.Parse(ngaysinh.Substring(11, 2)) : 0;
            if (ngayvao != "")
            {
                namht = int.Parse(ngayvao.Substring(6, 4));
                thanght = int.Parse(ngayvao.Substring(3, 2));
                ngayht = int.Parse(ngayvao.Substring(0, 2));
                gioht = int.Parse(ngayvao.Substring(11, 2));
            }
            if (nam == namht)
            {
                if (thang == thanght)
                {
                    if (ngay == ngayht) tuoi = "3/" + (gioht - gio);
                    else tuoi = "2/" + (ngayht - ngay);
                }
                else tuoi = "1/" + (thanght - thang);
            }
            else tuoi = "0/" + (namht - nam);
            return tuoi;
        }
        public long get_mabn_tudong(int yy, int userid, bool update)
        {
            int ma = 0;
            ds = get_data("select stt from " + user + ".capmabn where yy=" + yy + " and userid=" + userid);
            if (ds.Tables[0].Rows.Count > 0) ma = int.Parse(ds.Tables[0].Rows[0]["stt"].ToString());
            ma += 1;
            if (update) upd_capmabn(yy, userid, ma);
            return ma;
        }
        public int get_sttkham(string ngay)
        {
            int ma = 0;
            ds = get_data("select stt from " + user + ".capstt where ngay='" + ngay + "'");
            if (ds.Tables[0].Rows.Count > 0) ma = int.Parse(ds.Tables[0].Rows[0]["stt"].ToString());
            ma += 1;
            upd_capsttkham(ngay, ma);
            return ma;
        }
        private void upd_capsttkham(string ngay,int m_stt)
        {
            sql = "update " + user + ".capstt set stt=:m_stt where ngay=:ngay";
            con = new NpgsqlConnection(sConn);
            con.Open();
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
            cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
            int irec = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (irec == 0)
            {
                sql = "insert into " + user + ".capstt(stt,ngay) values (:m_stt,:ngay)";
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            con.Close(); con.Dispose();
        }
        public void upd_capmabn(int m_yy, int m_userid, int m_stt)
        {
            sql = "update " + user + ".capmabn set stt=:m_stt where yy=:m_yy and userid=:m_userid";
            con = new NpgsqlConnection(sConn);
            con.Open();
            cmd = new NpgsqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
            cmd.Parameters.Add("m_yy", NpgsqlDbType.Numeric).Value = m_yy;
            cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
            int irec = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (irec == 0)
            {
                sql = "insert into " + user + ".capmabn(yy,stt,userid) values (:m_yy,:m_stt,:m_userid)";
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_yy", NpgsqlDbType.Numeric).Value = m_yy;
                cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            con.Close(); con.Dispose();
        }
        public void upd_d_danhmuc(long m_ma, string m_ten,string m_table,string m_field)
        {
            sql = "update " + user + "."+m_table+" set "+m_field+"=:m_ten,ngayud=now() where id=:m_ma";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + "." + m_table + "(id," + m_field + ",ngayud) values (:m_ma,:m_ten,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, m_table);
            }
            finally 
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        #region The kho
        public bool upd_mabd(int d_mabd)
        {
            sql = "update " + user + ".d_mabd set mabd=:d_mabd where mabd=:d_mabd";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_mabd(mabd) values (:d_mabd)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_mabd");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public DataSet get_tondau(DataSet ds, DataTable dt, string d_mmyy, int d_makho, int d_manguon)
        {
            DataRow r1, r2, r3;
            DataRow[] dr;
            sql = "select a.mabd,sum(a.tondau) as tondau,sum(a.tondau*a.giamua) as sttondau from " + user + ".d_tonkhoct_"+d_mmyy+" a";
            sql += " where a.tondau<>0";
            if (d_makho != -1) sql += " and a.makho=" + d_makho;
            if (d_manguon != -1) sql += " and a.manguon=" + d_manguon;
            sql += " and a.mabd in (select mabd from " + user + ".d_mabd) group by a.mabd";
            foreach (DataRow r in get_data(sql).Tables[0].Rows)
            {
                r1 = getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
                if (r1 != null)
                {
                    r3 = getrowbyid(ds.Tables[0], "mabd=" + int.Parse(r["mabd"].ToString()));
                    if (r3 == null)
                    {
                        r2 = ds.Tables[0].NewRow();
                        r2["yymmdd"] = "";
                        r2["ngay"] = "";
                        r2["sonhap"] = "";
                        r2["soxuat"] = "";
                        r2["mabd"] = r["mabd"].ToString();
                        r2["ma"] = r1["ma"].ToString();
                        r2["ten"] = r1["ten"].ToString().Trim() + " " + r1["hamluong"].ToString();
                        r2["tenhc"] = r1["tenhc"].ToString();
                        r2["dang"] = r1["dang"].ToString();
                        r2["tenhang"] = r1["tenhang"].ToString();
                        r2["tennuoc"] = r1["tennuoc"].ToString();
                        r2["diengiai"] = "Tồn đầu";
                        r2["tondau"] = r["tondau"].ToString();
                        r2["sttondau"] = r["sttondau"].ToString();
                        r2["slnhap"] = 0;
                        r2["stnhap"] = 0;
                        r2["slxuat"] = 0;
                        r2["stxuat"] = 0;
                        r2["toncuoi"] = r["tondau"].ToString();
                        r2["sttoncuoi"] = r["sttondau"].ToString();
                        ds.Tables[0].Rows.Add(r2);
                    }
                    else
                    {
                        dr = ds.Tables[0].Select("mabd=" + int.Parse(r["mabd"].ToString()));
                        if (dr.Length > 0)
                        {
                            dr[0]["tondau"] = decimal.Parse(dr[0]["tondau"].ToString()) + decimal.Parse(r["tondau"].ToString());
                            dr[0]["sttondau"] = decimal.Parse(dr[0]["sttondau"].ToString()) + decimal.Parse(r["sttondau"].ToString());
                        }
                    }
                }
            }
            ds.AcceptChanges();
            return ds;
        }
        public DataSet get_nhap(DataSet ds, DataTable dt, DataTable dtkho, string d_tu, string d_den, string d_yy, int d_makho, int d_manguon)
        {
            DataRow r1, r2, r3;
            string s_diengiai = "", d_mmyy;
            int i_tu = int.Parse(d_tu), i_den = int.Parse(d_den);
            for (int i = i_tu; i <= i_den; i++)
            {
                d_mmyy = i.ToString().PadLeft(2, '0') + d_yy;
                if (bMmyy(d_mmyy))
                {
                    sql = "select to_char(a.ngayhd,'dd/mm/yyyy') as ngay,a.sohd as so,";
                    sql += "b.mabd,b.handung,b.soluong,b.sotien+b.sotien*b.vat/100 as sotien,a.loai,c.ten as diengiai,b.giamua,a.nguoigiao ";
                    sql += " from " + user + ".d_nhapll_"+d_mmyy+" a inner join " + user + ".d_nhapct_"+d_mmyy+" b on a.id=b.id ";
                    sql += " left join " + user + ".d_dmnx c on a.madv=c.id";
                    sql += " where b.mabd in (select mabd from " + user + ".d_mabd) ";
                    if (d_makho != -1) sql += " and a.makho=" + d_makho;
                    if (d_manguon != -1) sql += " and a.manguon=" + d_manguon;
                    sql += " order by a.ngayhd";
                    foreach (DataRow r in get_data(sql).Tables[0].Rows)
                    {
                        r1 = getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
                        if (r1 != null)
                        {
                            r2 = ds.Tables[0].NewRow();
                            r2["yymmdd"] = r["ngay"].ToString().Substring(8, 2) + r["ngay"].ToString().Substring(3, 2) + r["ngay"].ToString().Substring(0, 2);
                            r2["ngay"] = r["ngay"].ToString();
                            r2["sonhap"] = r["so"].ToString();
                            r2["soxuat"] = "";
                            r2["mabd"] = r["mabd"].ToString();
                            r2["ma"] = r1["ma"].ToString();
                            r2["ten"] = r1["ten"].ToString().Trim() + " " + r1["hamluong"].ToString();
                            r2["tenhc"] = r1["tenhc"].ToString();
                            r2["dang"] = r1["dang"].ToString();
                            r2["tenhang"] = r1["tenhang"].ToString();
                            r2["tennuoc"] = r1["tennuoc"].ToString();
                            switch (r["loai"].ToString())
                            {
                                case "M": r2["diengiai"] = "Nhập mới: ";
                                    break;
                                case "T": r2["diengiai"] = "Tái nhập: ";
                                    break;
                                default: r2["diengiai"] = "Bệnh nhân trả thuốc";
                                    break;
                            }
                            r2["diengiai"] += (r["loai"].ToString() == "N") ? r["nguoigiao"].ToString() : r["diengiai"].ToString();
                            r2["tondau"] = 0;
                            r2["sttondau"] = 0;
                            r2["slnhap"] = r["soluong"].ToString();
                            r2["stnhap"] = r["sotien"].ToString();
                            r2["slxuat"] = 0;
                            r2["stxuat"] = 0;
                            r2["toncuoi"] = r["soluong"].ToString();
                            r2["sttoncuoi"] = r["sotien"].ToString();
                            r2["handung"] = r["handung"].ToString();
                            r2["dongia"] = r["giamua"].ToString();
                            ds.Tables[0].Rows.Add(r2);
                        }
                    }
                    if (d_makho != -1)
                    {
                        sql = "select a.sophieu as so,to_char(a.ngay,'dd/mm/yyyy') as ngay,a.lydo,a.loai,a.khox,a.khon,b.mabd,b.soluong,b.soluong*b.giamua as sotien,b.giamua,b.handung ";
                        sql += " from " + user + ".d_xuatll a," + user + ".d_xuatct b ";
                        sql += " where a.id=b.id and a.loai in ('CK') and a.khon=" + d_makho;
                        if (d_manguon != -1) sql += " and b.manguon=" + d_manguon;
                        sql += " and a.mmyy='" + d_mmyy + "'";
                        sql += " and b.mabd in (select mabd from " + user + ".d_mabd) order by a.ngay";
                        foreach (DataRow r in get_data(sql).Tables[0].Rows)
                        {
                            r1 = getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
                            if (r1 != null)
                            {
                                r2 = ds.Tables[0].NewRow();
                                r2["yymmdd"] = r["ngay"].ToString().Substring(8, 2) + r["ngay"].ToString().Substring(3, 2) + r["ngay"].ToString().Substring(0, 2);
                                r2["ngay"] = r["ngay"].ToString();
                                r2["sonhap"] = r["so"].ToString();
                                r2["soxuat"] = "";
                                r2["mabd"] = r["mabd"].ToString();
                                r2["ma"] = r1["ma"].ToString();
                                r2["ten"] = r1["ten"].ToString().Trim() + " " + r1["hamluong"].ToString();
                                r2["tenhc"] = r1["tenhc"].ToString();
                                r2["dang"] = r1["dang"].ToString();
                                r2["tenhang"] = r1["tenhang"].ToString();
                                r2["tennuoc"] = r1["tennuoc"].ToString();
                                r3 = getrowbyid(dtkho, "id=" + int.Parse(r["khox"].ToString()));
                                if (r3 != null) s_diengiai = "Nhập từ :" + r3["ten"].ToString();
                                r2["diengiai"] = s_diengiai;
                                r2["tondau"] = 0;
                                r2["sttondau"] = 0;
                                r2["slnhap"] = r["soluong"].ToString();
                                r2["stnhap"] = r["sotien"].ToString();
                                r2["slxuat"] = 0;
                                r2["stxuat"] = 0;
                                r2["toncuoi"] = r["soluong"].ToString();
                                r2["sttoncuoi"] = r["sotien"].ToString();
                                r2["handung"] = r["handung"].ToString();
                                r2["dongia"] = r["giamua"].ToString();
                                ds.Tables[0].Rows.Add(r2);
                            }
                        }
                    }
                }
            }
            ds.AcceptChanges();
            return ds;
        }
        public DataSet get_xuat(DataSet ds, DataTable dt, string d_tu, string d_den, string d_yy, int d_makho, int d_manguon)
        {
            DataTable dtnx = get_data("select id,ten from " + user + ".d_dmnx").Tables[0];
            DataRow r1, r2, r3;
            string s_diengiai = "", d_mmyy = "";
            int i_tu = int.Parse(d_tu), i_den = int.Parse(d_den);
            for (int i = i_tu; i <= i_den; i++)
            {
                d_mmyy = i.ToString().PadLeft(2, '0') + d_yy;
                if (bMmyy(d_mmyy))
                {
                    sql = "select a.sophieu as so,to_char(a.ngay,'dd/mm/yyyy') as ngay,a.lydo,a.loai,a.khox,a.khon,b.mabd,b.handung,b.giamua,sum(b.soluong) as soluong,sum(b.soluong*b.giamua) as sotien ";
                    sql += " from " + user + ".d_xuatll a," + user + ".d_xuatct b";
                    sql += " where a.id=b.id and a.mmyy='" + d_mmyy + "'";
                    if (d_makho != -1) sql += " and a.loai in ('HT','XH') and a.khox=" + d_makho;
                    if (d_manguon != -1) sql += " and b.manguon=" + d_manguon;
                    sql += " and b.mabd in (select mabd from " + user + ".d_mabd)";
                    sql += " group by a.sophieu,to_char(a.ngay,'dd/mm/yyyy'),a.lydo,a.loai,a.khox,a.khon,b.mabd,b.handung,b.giamua";
                    foreach (DataRow r in get_data(sql).Tables[0].Select("true", "ngay"))
                    {
                        r1 = getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
                        if (r1 != null)
                        {
                            r2 = ds.Tables[0].NewRow();
                            r2["yymmdd"] = r["ngay"].ToString().Substring(8, 2) + r["ngay"].ToString().Substring(3, 2) + r["ngay"].ToString().Substring(0, 2);
                            r2["ngay"] = r["ngay"].ToString();
                            r2["sonhap"] = "";
                            r2["soxuat"] = r["so"].ToString();
                            r2["mabd"] = r["mabd"].ToString();
                            r2["ma"] = r1["ma"].ToString();
                            r2["ten"] = r1["ten"].ToString().Trim() + " " + r1["hamluong"].ToString();
                            r2["tenhc"] = r1["tenhc"].ToString();
                            r2["dang"] = r1["dang"].ToString();
                            r2["tenhang"] = r1["tenhang"].ToString();
                            r2["tennuoc"] = r1["tennuoc"].ToString();
                            switch (r["loai"].ToString())
                            {
                                case "HT":
                                    s_diengiai = "Hoàn trả : ";
                                    r3 = getrowbyid(dtnx, "id=" + int.Parse(r["khon"].ToString()));
                                    if (r3 != null) s_diengiai += r3["ten"].ToString();
                                    break;
                                case "XH":
                                    s_diengiai = r["lydo"].ToString().Trim();
                                    break;
                            }
                            r2["diengiai"] = s_diengiai;
                            r2["tondau"] = 0;
                            r2["sttondau"] = 0;
                            r2["slnhap"] = 0;
                            r2["stnhap"] = 0;
                            r2["slxuat"] = r["soluong"].ToString();
                            r2["stxuat"] = r["sotien"].ToString();
                            r2["toncuoi"] = r["soluong"].ToString();
                            r2["sttoncuoi"] = r["sotien"].ToString();
                            r2["handung"] = r["handung"].ToString();
                            r2["dongia"] = r["giamua"].ToString();
                            ds.Tables[0].Rows.Add(r2);
                        }
                    }
                    sql = "select a.mabn as so,to_char(a.ngay,'dd/mm/yyyy') as ngay,a.hoten as lydo,b.mabd,b.handung,b.giaban,sum(b.soluong) as soluong,sum(b.soluong*b.giaban) as sotien ";
                    sql += " from " + user + ".d_toathuocll_" + d_mmyy + " a," + user + ".d_toathuocct_" + d_mmyy + " b";
                    sql += " where a.id=b.id ";
                    if (d_makho != -1) sql +=" and b.makho=" + d_makho;
                    if (d_manguon != -1) sql += " and b.manguon=" + d_manguon;
                    sql += " and b.mabd in (select mabd from " + user + ".d_mabd)";
                    sql += " group by a.mabn,to_char(a.ngay,'dd/mm/yyyy'),a.hoten,b.mabd,b.handung,b.giaban";
                    foreach (DataRow r in get_data(sql).Tables[0].Select("true", "ngay"))
                    {
                        r1 = getrowbyid(dt, "id=" + int.Parse(r["mabd"].ToString()));
                        if (r1 != null)
                        {
                            r2 = ds.Tables[0].NewRow();
                            r2["yymmdd"] = r["ngay"].ToString().Substring(8, 2) + r["ngay"].ToString().Substring(3, 2) + r["ngay"].ToString().Substring(0, 2);
                            r2["ngay"] = r["ngay"].ToString();
                            r2["sonhap"] = "";
                            r2["soxuat"] = r["so"].ToString();
                            r2["mabd"] = r["mabd"].ToString();
                            r2["ma"] = r1["ma"].ToString();
                            r2["ten"] = r1["ten"].ToString().Trim() + " " + r1["hamluong"].ToString();
                            r2["tenhc"] = r1["tenhc"].ToString();
                            r2["dang"] = r1["dang"].ToString();
                            r2["tenhang"] = r1["tenhang"].ToString();
                            r2["tennuoc"] = r1["tennuoc"].ToString();
                            r2["diengiai"] = "Bán thuốc cho bệnh nhân";
                            r2["tondau"] = 0;
                            r2["sttondau"] = 0;
                            r2["slnhap"] = 0;
                            r2["stnhap"] = 0;
                            r2["slxuat"] = r["soluong"].ToString();
                            r2["stxuat"] = r["sotien"].ToString();
                            r2["toncuoi"] = r["soluong"].ToString();
                            r2["sttoncuoi"] = r["sotien"].ToString();
                            r2["handung"] = r["handung"].ToString();
                            r2["dongia"] = r["giaban"].ToString();
                            ds.Tables[0].Rows.Add(r2);
                        }
                    }
                }
            }
            ds.AcceptChanges();
            return ds;
        }
        public void updrec_thekho(DataTable dt, int mabd, string ma, string ten, string tenhc, string dang, string tenhang, decimal soluong, long stt)
        {
            string exp = "mabd=" + mabd;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["mabd"] = mabd;
                nrow["ma"] = ma;
                nrow["ten"] = ten;
                nrow["tenhc"] = tenhc;
                nrow["dang"] = dang;
                nrow["tenhang"] = tenhang;
                nrow["toncuoi"] = soluong;
                nrow["stt"] = stt;
                dt.Rows.Add(nrow);
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                if (dr.Length > 0) dr[0]["toncuoi"] = decimal.Parse(dr[0]["toncuoi"].ToString()) + soluong;
            }
        }
        public DataSet ins_items(DataSet dsxml, DataRow[] dr, int i, decimal soluong, decimal sotien)
        {
            DataRow r2;
            r2 = dsxml.Tables[0].NewRow();
            r2["yymmdd"] = dr[i]["yymmdd"].ToString();
            r2["ngay"] = dr[i]["ngay"].ToString();
            r2["sonhap"] = dr[i]["sonhap"].ToString();
            r2["soxuat"] = dr[i]["soxuat"].ToString();
            r2["mabd"] = dr[i]["mabd"].ToString();
            r2["ma"] = dr[i]["ma"].ToString();
            r2["ten"] = dr[i]["ten"].ToString().Trim();
            r2["tenhc"] = dr[i]["tenhc"].ToString();
            r2["dang"] = dr[i]["dang"].ToString();
            r2["tenhang"] = dr[i]["tenhang"].ToString();
            r2["tennuoc"] = dr[i]["tennuoc"].ToString();
            r2["diengiai"] = dr[i]["diengiai"].ToString();
            r2["tondau"] = dr[i]["tondau"].ToString();
            r2["sttondau"] = dr[i]["sttondau"].ToString();
            r2["slnhap"] = dr[i]["slnhap"].ToString();
            r2["stnhap"] = dr[i]["stnhap"].ToString();
            r2["slxuat"] = dr[i]["slxuat"].ToString();
            r2["stxuat"] = dr[i]["stxuat"].ToString();
            if (dr[i]["dongia"].ToString() != "") r2["dongia"] = dr[i]["dongia"].ToString();
            r2["handung"] = dr[i]["handung"].ToString();
            r2["losx"] = dr[i]["losx"].ToString();
            r2["toncuoi"] = soluong;
            r2["sttoncuoi"] = sotien;
            dsxml.Tables[0].Rows.Add(r2);
            return dsxml;
        }
        #endregion
        #region Excel
        public void check_process_Excel()
        {
            Process[] processes = Process.GetProcesses();
            if (processes.Length > 1)
            {
                int i = 0;
                for (int n = 0; n <= processes.Length - 1; n++)
                {
                    if (((Process)processes[n]).ProcessName == "EXCEL")
                    {
                        i++;
                        ((Process)processes[n]).Kill();
                    }
                }
            }
        }
        public string Export_Excel(DataTable dt, string tenfile)
        {
            try
            {
                string dirPath = AppDomain.CurrentDomain.BaseDirectory + "Excel";
                string filePath = dirPath + "\\" + tenfile + ".xls";
                if (!System.IO.Directory.Exists(dirPath)) System.IO.Directory.CreateDirectory(dirPath);
                System.IO.StreamWriter sw = new System.IO.StreamWriter(filePath, false, System.Text.Encoding.Unicode);
                string astr = "";
                astr = "<Table>";//"<Table border=1>";
                astr = astr + "<tr>";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    astr = astr + "<th>";
                    astr = astr + dt.Columns[i].ColumnName;
                    astr = astr + "</th>";
                }
                astr = astr + "</tr>";
                sw.Write(astr);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    astr = "<tr>";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        astr = astr + "<td>";
                        astr = astr + dt.Rows[i][j].ToString();
                        astr = astr + "</td>";
                    }
                    astr = astr + "</tr>";
                    sw.Write(astr);
                }
                astr = "</Table>";
                sw.Write(astr);
                sw.Close();
                return filePath;
            }
            catch (Exception ex)
            {
                upd_error(ex.Message, sComputer, tenfile);
                return "";
            }
        }
        #endregion
        public void upd_d_dmkho(long m_ma, string m_ten, int m_khott)
        {
            sql = "update " + user + ".d_dmkho set ten=:m_ten,khott=:m_khott,ngayud=now() where id=:m_ma";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("m_khott", NpgsqlDbType.Numeric).Value = m_khott;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_dmkho(id,ten,khott,ngayud) values (:m_ma,:m_ten,:m_khott,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.Parameters.Add("m_khott", NpgsqlDbType.Numeric).Value = m_khott;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "d_dmkho");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_d_hoatchat(long m_id, string m_ma, string m_ten, int m_hide)
        {
            sql = "update " + user + ".d_dmhoatchat set mahc=:m_ma,tenhc=:m_ten,hide=:m_hide where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar,10).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("m_hide", NpgsqlDbType.Numeric).Value = m_hide;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_dmhoatchat(id,mahc,tenhc,hide) values (:m_id,:m_ma,:m_ten,:m_hide)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar, 10).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.Parameters.Add("m_hide", NpgsqlDbType.Numeric).Value = m_hide;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "d_dmhoatchat");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_dmbs(string m_ma, string m_hoten, string m_nhom,int i_nghiviec)
        {
            sql = "update " + user + ".dmbs set hoten=:m_hoten,nhom=:m_nhom,nghiviec=:i_nghiviec where ma=:m_ma";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar).Value = m_ma;
                cmd.Parameters.Add("m_hoten", NpgsqlDbType.Text, 10).Value = m_hoten;
                //cmd.Parameters.Add("m_mapk", NpgsqlDbType.Varchar).Value = m_mapk;
                cmd.Parameters.Add("m_nhom", NpgsqlDbType.Varchar).Value = m_nhom;
                cmd.Parameters.Add("i_nghiviec", NpgsqlDbType.Numeric,1).Value = i_nghiviec;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dmbs(ma,hoten,nhom,nghiviec) values (:m_ma,:m_hoten,:m_nhom,:i_nghiviec)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar).Value = m_ma;
                    cmd.Parameters.Add("m_hoten", NpgsqlDbType.Text, 10).Value = m_hoten;
                    //cmd.Parameters.Add("m_mapk", NpgsqlDbType.Varchar).Value = m_mapk;
                    cmd.Parameters.Add("m_nhom", NpgsqlDbType.Varchar).Value = m_nhom;
                    cmd.Parameters.Add("i_nghiviec", NpgsqlDbType.Numeric, 1).Value = i_nghiviec;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "dmbs");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_dmpk(string m_ma, string m_ten, long mavp, string m_mabs, string m_form)
        {
            sql = "update " + user + ".btdkp set tenkp=:m_ten, mavp=:mavp, mabs=:m_mabs, form=:m_form where makp=:m_ma";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("mavp", NpgsqlDbType.Numeric).Value = mavp;
                cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar).Value = m_mabs;
                cmd.Parameters.Add("m_form", NpgsqlDbType.Varchar).Value = m_form;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".btdkp(makp,tenkp,mavp,mabs,form) values (:m_ma,:m_ten,:mavp,:m_mabs,:m_form)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.Parameters.Add("mavp", NpgsqlDbType.Numeric).Value = mavp;
                    cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar).Value = m_mabs;
                    cmd.Parameters.Add("m_form", NpgsqlDbType.Varchar).Value = m_form;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "btdkp");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_dmgiavp(long m_id, string m_ten, decimal d_dongia, string m_form, int pttt)
        {
            sql = "update " + user + ".dmgiavp set ten=:m_ten, dongia=:d_dongia, form=:m_form,pttt=:pttt where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                cmd.Parameters.Add("m_form", NpgsqlDbType.Varchar).Value = m_form;
                cmd.Parameters.Add("pttt", NpgsqlDbType.Varchar).Value = pttt;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dmgiavp(id,ten,dongia,form,pttt) values (:m_id,:m_ten,:d_dongia,:m_form,:pttt)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                    cmd.Parameters.Add("m_form", NpgsqlDbType.Varchar).Value = m_form;
                    cmd.Parameters.Add("pttt", NpgsqlDbType.Varchar).Value = pttt;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "dmgiavp");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_viettat(long m_id, string m_tenvt, string m_diengiai)
        {
            sql = "update " + user + ".dmviettat set tenvt=:m_tenvt, diengiai=:m_diengiai where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_tenvt", NpgsqlDbType.Varchar).Value = m_tenvt;
                cmd.Parameters.Add("m_diengiai", NpgsqlDbType.Text).Value = m_diengiai;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                { 
                    sql = "insert into " + user + ".dmviettat(id,tenvt,diengiai) values (:m_id,:m_tenvt,:m_diengiai)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_tenvt", NpgsqlDbType.Varchar).Value = m_tenvt;
                    cmd.Parameters.Add("m_diengiai", NpgsqlDbType.Text).Value = m_diengiai;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "dmviettat");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_dmdoituong(int m_ma, string m_ten)
        {
            sql = "update " + user + ".doituong set doituong=:m_ten where madoituong=:m_ma";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".doituong(madoituong,doituong) values (:m_ma,:m_ten)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Numeric).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "doituong");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_diungthuoc(string m_mabn,string m_mahc, int m_mucdo)
        {
            sql = "update " + user + ".d_diungthuoc set mucdo=:m_mucdo where mabn=:m_mabn and mahc=:m_mahc";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("m_mahc", NpgsqlDbType.Varchar).Value = m_mahc;
                cmd.Parameters.Add("m_mucdo", NpgsqlDbType.Numeric).Value = m_mucdo;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_diungthuoc(mabn,mahc,mucdo) values (:m_mabn,:m_mahc,:m_mucdo)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_mahc", NpgsqlDbType.Varchar).Value = m_mahc;
                    cmd.Parameters.Add("m_mucdo", NpgsqlDbType.Numeric).Value = m_mucdo;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "d_diungthuoc");
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public void upd_d_cachdung_dvt_quycach(long m_id,string m_ten, string m_table)
        {
            sql = "update " + user + "." + m_table + " set ten=:m_ten where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + "."+m_table+"(id,ten) values (:m_id,:m_ten)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, m_table);
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public bool upd_btdbn(string m_mabn, string m_hoten, string m_ngaysinh, string m_namsinh,int m_phai, string m_mann, string m_madantoc, string m_diachi,string m_cholam, string m_matt, string m_maqu, string m_maphuongxa, int m_userid)
        {
            m_hotenkdau = Hoten_khongdau(m_hoten);
            sql = "update " + user + ".btdbn set hoten=:m_hoten,";
            if (m_ngaysinh.Trim().Length == 10) sql += "ngaysinh=:m_ngaysinh,";
            else sql += "ngaysinh=null,";
            sql += "namsinh=:m_namsinh,phai=:m_phai,mann=:m_mann,madantoc=:m_madantoc,";
            sql += "diachi=:m_diachi,cholam=:m_cholam,matt=:m_matt,maqu=:m_maqu,";
            sql += "maphuongxa=:m_maphuongxa,userid=:m_userid,hotenkdau=:m_hotenkdau where mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_hoten", NpgsqlDbType.Text).Value = m_hoten;
                if (m_ngaysinh.Trim().Length == 10) cmd.Parameters.Add("m_ngaysinh", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngaysinh);
                cmd.Parameters.Add("m_namsinh", NpgsqlDbType.Varchar, 4).Value = m_namsinh;
                cmd.Parameters.Add("m_phai", NpgsqlDbType.Numeric).Value = m_phai;
                cmd.Parameters.Add("m_mann", NpgsqlDbType.Varchar, 2).Value = m_mann;
                cmd.Parameters.Add("m_madantoc", NpgsqlDbType.Varchar, 2).Value = m_madantoc;
                cmd.Parameters.Add("m_diachi", NpgsqlDbType.Text).Value = m_diachi;
                cmd.Parameters.Add("m_cholam", NpgsqlDbType.Text).Value = m_cholam;
                cmd.Parameters.Add("m_matt", NpgsqlDbType.Varchar, 3).Value = m_matt;
                cmd.Parameters.Add("m_maqu", NpgsqlDbType.Varchar, 5).Value = m_maqu;
                cmd.Parameters.Add("m_maphuongxa", NpgsqlDbType.Varchar, 7).Value = m_maphuongxa;
                cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                cmd.Parameters.Add("m_hotenkdau", NpgsqlDbType.Text).Value = m_hotenkdau;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    if (m_ngaysinh.Trim().Length == 10)
                    {
                        sql = "insert into " + user + ".btdbn (mabn,hoten,ngaysinh,namsinh,phai,mann,madantoc,diachi,cholam,matt,maqu,maphuongxa,userid,hotenkdau,ngayud)";
                        sql += " values (:m_mabn,:m_hoten,:m_ngaysinh,:m_namsinh,:m_phai,:m_mann,:m_madantoc,:m_diachi,:m_cholam,:m_matt,:m_maqu,:m_maphuongxa,:m_userid,:m_hotenkdau,now())";
                    }
                    else
                    {
                        sql = "insert into " + user + ".btdbn (mabn,hoten,namsinh,phai,mann,madantoc,diachi,cholam,matt,maqu,maphuongxa,userid,hotenkdau,ngayud)";
                        sql += " values (:m_mabn,:m_hoten,:m_namsinh,:m_phai,:m_mann,:m_madantoc,:m_diachi,:m_cholam,:m_matt,:m_maqu,:m_maphuongxa,:m_userid,:m_hotenkdau,now())";
                    }
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_hoten", NpgsqlDbType.Text).Value = m_hoten;
                    if (m_ngaysinh.Trim().Length == 10) cmd.Parameters.Add("m_ngaysinh", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngaysinh);
                    cmd.Parameters.Add("m_namsinh", NpgsqlDbType.Varchar, 4).Value = m_namsinh;
                    cmd.Parameters.Add("m_phai", NpgsqlDbType.Numeric).Value = m_phai;
                    cmd.Parameters.Add("m_mann", NpgsqlDbType.Varchar, 2).Value = m_mann;
                    cmd.Parameters.Add("m_madantoc", NpgsqlDbType.Varchar, 2).Value = m_madantoc;
                    cmd.Parameters.Add("m_diachi", NpgsqlDbType.Text).Value = m_diachi;
                    cmd.Parameters.Add("m_cholam", NpgsqlDbType.Text).Value = m_cholam;
                    cmd.Parameters.Add("m_matt", NpgsqlDbType.Varchar, 3).Value = m_matt;
                    cmd.Parameters.Add("m_maqu", NpgsqlDbType.Varchar, 5).Value = m_maqu;
                    cmd.Parameters.Add("m_maphuongxa", NpgsqlDbType.Varchar, 7).Value = m_maphuongxa;
                    cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                    cmd.Parameters.Add("m_hotenkdau", NpgsqlDbType.Text).Value = m_hotenkdau;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "Btdbn " + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_benhanpk(string m_mabn, long m_maql, string m_makp, string m_ngay,
            int m_lanthu, string m_tuoivao, int m_madoituong, string m_sothe, string m_denngay,string m_trieuchung, string m_chandoan,string m_xutri,
            string m_maicd, string m_mabs, int m_userid, int m_done,int m_stt,int m_hen)
        {
            sql = "update " + user + ".benhanpk set makp=:m_makp, ngay=:m_ngay, lanthu=:m_lanthu, tuoivao=:m_tuoivao,";
            if (m_denngay.Trim() != "") sql += "denngay=:m_denngay,";
            sql += "madoituong=:m_madoituong, sothe=:m_sothe, trieuchung=:m_trieuchung, chandoan=:m_chandoan, xutri=:m_xutri, maicd=:m_maicd,mabs=:m_mabs,";
            sql += "userid=:m_userid,ngayud=now(),done=:m_done,stt=:m_stt,hen=:m_hen where maql=:m_maql";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                cmd.Parameters.Add("m_lanthu", NpgsqlDbType.Numeric).Value = m_lanthu;
                cmd.Parameters.Add("m_tuoivao", NpgsqlDbType.Varchar,4).Value = m_tuoivao;
                cmd.Parameters.Add("m_madoituong", NpgsqlDbType.Numeric).Value = m_madoituong;
                cmd.Parameters.Add("m_sothe", NpgsqlDbType.Varchar,20).Value = m_sothe;
                if (m_denngay.Trim() != "") cmd.Parameters.Add("m_denngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_denngay);
                cmd.Parameters.Add("m_trieuchung", NpgsqlDbType.Text).Value = m_trieuchung;
                cmd.Parameters.Add("m_chandoan", NpgsqlDbType.Text).Value = m_chandoan;
                cmd.Parameters.Add("m_xutri", NpgsqlDbType.Text).Value = m_xutri;
                cmd.Parameters.Add("m_maicd", NpgsqlDbType.Varchar, 9).Value = m_maicd;
                cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar, 4).Value = m_mabs;
                cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("m_done", NpgsqlDbType.Numeric).Value = m_done;
                cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                cmd.Parameters.Add("m_hen", NpgsqlDbType.Numeric).Value = m_hen;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    if (m_denngay.Trim() != "")
                    {
                        sql = "insert into " + user + ".benhanpk(mabn,maql,makp,ngay,lanthu,tuoivao,madoituong,sothe,denngay,trieuchung,chandoan,xutri,maicd,mabs,userid,ngayud,done,stt,hen)";
                        sql += " values (:m_mabn,:m_maql,:m_makp,:m_ngay,:m_lanthu,:m_tuoivao,:m_madoituong,:m_sothe,:m_denngay,:m_trieuchung,:m_chandoan,:m_xutri,:m_maicd,:m_mabs,:m_userid,now(),:m_done,:m_stt,:m_hen)";
                    }
                    else 
                    {
                        sql = "insert into " + user + ".benhanpk(mabn,maql,makp,ngay,lanthu,tuoivao,madoituong,sothe,denngay,trieuchung,chandoan,xutri,maicd,mabs,userid,ngayud,done,stt,hen)";
                        sql += " values (:m_mabn,:m_maql,:m_makp,:m_ngay,:m_lanthu,:m_tuoivao,:m_madoituong,:m_sothe,null,:m_trieuchung,:m_chandoan,:m_xutri,:m_maicd,:m_mabs,:m_userid,now(),:m_done,:m_stt,:m_hen)";
                    }
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                    cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                    cmd.Parameters.Add("m_lanthu", NpgsqlDbType.Numeric).Value = m_lanthu;
                    cmd.Parameters.Add("m_tuoivao", NpgsqlDbType.Varchar, 4).Value = m_tuoivao;
                    cmd.Parameters.Add("m_madoituong", NpgsqlDbType.Numeric).Value = m_madoituong;
                    cmd.Parameters.Add("m_sothe", NpgsqlDbType.Varchar, 20).Value = m_sothe;
                    if (m_denngay.Trim() != "") cmd.Parameters.Add("m_denngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_denngay);
                    cmd.Parameters.Add("m_trieuchung", NpgsqlDbType.Text).Value = m_trieuchung;
                    cmd.Parameters.Add("m_chandoan", NpgsqlDbType.Text).Value = m_chandoan;
                    cmd.Parameters.Add("m_xutri", NpgsqlDbType.Text).Value = m_xutri;
                    cmd.Parameters.Add("m_maicd", NpgsqlDbType.Varchar, 9).Value = m_maicd;
                    cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar, 4).Value = m_mabs;
                    cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("m_done", NpgsqlDbType.Numeric).Value = m_done;
                    cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                    cmd.Parameters.Add("m_hen", NpgsqlDbType.Numeric).Value = m_hen;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "benhanpk_" + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_pttt(long m_id, string m_mabn, long m_maql, string m_makp, string m_mapmo, string m_ngay,
            string m_maicdt, string m_chandoant, string m_maicds, string m_chandoans, string m_mapt, string m_tenpt, string m_phuongphap, int m_tinhinh,
            int m_taibien, int m_tuvong, int m_userid, string m_ngaykt, string m_tuongtrinh, string m_ptv, string m_phu1, string m_phu2, string m_gayme1, string m_gayme2, int mavp)
        {
            sql = "update " + user + ".pttt set mabn=:m_mabn, maql=:m_maql, makp=:m_makp, mapmo=:m_mapmo, ngay=:m_ngay,";
            sql += "maicdt=:m_maicdt, chandoant=:m_chandoant, maicds=:m_maicds, chandoans=:m_chandoans, mapt=:m_mapt,";
            sql += "tenpt=:m_tenpt, phuongphap=:m_phuongphap, tinhhinh=:m_tinhinh,";
            sql += "taibien=:m_taibien, tuvong=:m_tuvong, userid=:m_userid, ngayud=now(), ngaykt=:m_ngaykt, ";
            sql += "tuongtrinh=:m_tuongtrinh, ptv=:m_ptv, phu1=:m_phu1, phu2=:m_phu2, gayme1=:m_gayme1, gayme2=:m_gayme2,mavp=:mavp where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar, 8).Value = m_mabn;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar,2).Value = m_makp;
                cmd.Parameters.Add("m_mapmo", NpgsqlDbType.Varchar, 2).Value = m_mapmo;
                cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                cmd.Parameters.Add("m_maicdt", NpgsqlDbType.Varchar,8).Value = m_maicdt;
                cmd.Parameters.Add("m_chandoant", NpgsqlDbType.Text).Value = m_chandoant;
                cmd.Parameters.Add("m_maicds", NpgsqlDbType.Varchar,8).Value = m_maicds;
                cmd.Parameters.Add("m_chandoans", NpgsqlDbType.Text).Value = m_chandoans;
                cmd.Parameters.Add("m_mapt", NpgsqlDbType.Varchar,9).Value = m_mapt;
                cmd.Parameters.Add("m_tenpt", NpgsqlDbType.Text).Value = m_tenpt;
                cmd.Parameters.Add("m_phuongphap", NpgsqlDbType.Varchar,2).Value = m_phuongphap;
                cmd.Parameters.Add("m_tinhinh", NpgsqlDbType.Numeric).Value = m_tinhinh;
                cmd.Parameters.Add("m_taibien", NpgsqlDbType.Numeric).Value = m_taibien;
                cmd.Parameters.Add("m_tuvong", NpgsqlDbType.Numeric).Value = m_tuvong;
                cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                cmd.Parameters.Add("m_ngaykt", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngaykt);
                cmd.Parameters.Add("m_tuongtrinh", NpgsqlDbType.Text).Value = m_tuongtrinh;
                cmd.Parameters.Add("m_ptv", NpgsqlDbType.Varchar, 4).Value = m_ptv;
                cmd.Parameters.Add("m_phu1", NpgsqlDbType.Varchar, 4).Value = m_phu1;
                cmd.Parameters.Add("m_phu2", NpgsqlDbType.Varchar, 4).Value = m_phu2;
                cmd.Parameters.Add("m_gayme1", NpgsqlDbType.Varchar, 4).Value = m_gayme1;
                cmd.Parameters.Add("m_gayme2", NpgsqlDbType.Varchar, 4).Value = m_gayme2;
                cmd.Parameters.Add("mavp", NpgsqlDbType.Numeric).Value = mavp;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".pttt(id,mabn,maql,makp,mapmo,ngay,maicdt,chandoant,maicds,chandoans,mapt,tenpt,phuongphap,tinhhinh,taibien,tuvong,userid,ngayud,ngaykt,tuongtrinh,ptv,phu1,phu2,gayme1,gayme2,mavp)";
                    sql += " values (:m_id,:m_mabn,:m_maql,:m_makp,:m_mapmo,:m_ngay,:m_maicdt,:m_chandoant,:m_maicds,:m_chandoans,:m_mapt,:m_tenpt,:m_phuongphap,:m_tinhinh,:m_taibien,:m_tuvong,:m_userid,now(),:m_ngaykt,:m_tuongtrinh,:m_ptv,:m_phu1,:m_phu2,:m_gayme1,:m_gayme2,:mavp)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar, 8).Value = m_mabn;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                    cmd.Parameters.Add("m_mapmo", NpgsqlDbType.Varchar, 2).Value = m_mapmo;
                    cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                    cmd.Parameters.Add("m_maicdt", NpgsqlDbType.Varchar, 8).Value = m_maicdt;
                    cmd.Parameters.Add("m_chandoant", NpgsqlDbType.Text).Value = m_chandoant;
                    cmd.Parameters.Add("m_maicds", NpgsqlDbType.Varchar, 8).Value = m_maicds;
                    cmd.Parameters.Add("m_chandoans", NpgsqlDbType.Text).Value = m_chandoans;
                    cmd.Parameters.Add("m_mapt", NpgsqlDbType.Varchar, 9).Value = m_mapt;
                    cmd.Parameters.Add("m_tenpt", NpgsqlDbType.Text).Value = m_tenpt;
                    cmd.Parameters.Add("m_phuongphap", NpgsqlDbType.Varchar, 2).Value = m_phuongphap;
                    cmd.Parameters.Add("m_tinhinh", NpgsqlDbType.Numeric).Value = m_tinhinh;
                    cmd.Parameters.Add("m_taibien", NpgsqlDbType.Numeric).Value = m_taibien;
                    cmd.Parameters.Add("m_tuvong", NpgsqlDbType.Numeric).Value = m_tuvong;
                    cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric).Value = m_userid;
                    cmd.Parameters.Add("m_ngaykt", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngaykt);
                    cmd.Parameters.Add("m_tuongtrinh", NpgsqlDbType.Text).Value = m_tuongtrinh;
                    cmd.Parameters.Add("m_ptv", NpgsqlDbType.Varchar, 4).Value = m_ptv;
                    cmd.Parameters.Add("m_phu1", NpgsqlDbType.Varchar, 4).Value = m_phu1;
                    cmd.Parameters.Add("m_phu2", NpgsqlDbType.Varchar, 4).Value = m_phu2;
                    cmd.Parameters.Add("m_gayme1", NpgsqlDbType.Varchar, 4).Value = m_gayme1;
                    cmd.Parameters.Add("m_gayme2", NpgsqlDbType.Varchar, 4).Value = m_gayme2;
                    cmd.Parameters.Add("mavp", NpgsqlDbType.Numeric).Value = mavp;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "pttt" + m_mabn);
                return false;
            }
            return true;
        }
        public void upd_dausinhton(long m_maql, decimal m_mach, decimal m_nhietdo, string m_huyetap, decimal m_nhiptho, decimal m_cannang)
        {
            sql = "update " + user + ".dausinhton set mach=:m_mach, nhietdo=:m_nhietdo, huyetap=:m_huyetap, nhiptho=:m_nhiptho, cannang=:m_cannang where maql=:m_maql";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("m_mach", NpgsqlDbType.Numeric).Value = m_mach;
                cmd.Parameters.Add("m_nhietdo", NpgsqlDbType.Numeric).Value = m_nhietdo;
                cmd.Parameters.Add("m_nhiptho", NpgsqlDbType.Numeric).Value = m_nhiptho;
                cmd.Parameters.Add("m_huyetap", NpgsqlDbType.Varchar, 7).Value = m_huyetap;
                cmd.Parameters.Add("m_cannang", NpgsqlDbType.Numeric).Value = m_cannang;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dausinhton(maql, mach, nhietdo, huyetap, nhiptho, cannang) values (:m_maql,:m_mach,:m_nhietdo,:m_huyetap,:m_nhiptho,:m_cannang)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("m_mach", NpgsqlDbType.Numeric).Value = m_mach;
                    cmd.Parameters.Add("m_nhietdo", NpgsqlDbType.Numeric).Value = m_nhietdo;
                    cmd.Parameters.Add("m_nhiptho", NpgsqlDbType.Numeric).Value = m_nhiptho;
                    cmd.Parameters.Add("m_huyetap", NpgsqlDbType.Varchar, 7).Value = m_huyetap;
                    cmd.Parameters.Add("m_cannang", NpgsqlDbType.Numeric).Value = m_cannang;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "dausinhton" + m_maql);
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public string getMabd(string table, string s_tenbd, int manhom)
        {
            string str = Mabd(s_tenbd, 3);
            int num = 1;
            try
            {
                sql = "select max(substr(ma,4)) from " + user + "." + table + " where substr(ma,1,3)='" + str + "' and nhom=" + manhom;
                num = int.Parse(get_data(sql).Tables[0].Rows[0][0].ToString()) + 1;
            }
            catch
            {
            }
            return (str + num.ToString().PadLeft(6 - str.Length, '0'));
        }
        public string getMahc(string s_tenhc)
        {
            string str = Mabd(s_tenhc, 3);
            int num = 1;
            try
            {
                sql = "select max(substr(mahc,4)) from " + user + ".d_dmhoatchat where substr(mahc,1,3)='" + str + "'";
                num = int.Parse(get_data(sql).Tables[0].Rows[0][0].ToString()) + 1;
            }
            catch
            {
            }
            return (str + num.ToString().PadLeft(6 - str.Length, '0'));
        }
        public string Mabd(string s, int so)
        {
            DataSet set = new DataSet();
            set.ReadXml(@"..\..\..\xml\khongdau.xml");
            string str = s.Trim().ToUpper();
            string str2 = "";
            for (int i = 0; i < str.Length; i++)
            {
                char ch = str[i];
                if (ch.ToString().Trim() != "")
                {
                    DataRow row = this.getrowbyid(set.Tables[0], "codau='" + str[i].ToString() + "'");
                    if (row != null)
                    {
                        str2 = str2 + row[1].ToString();
                    }
                    else
                    {
                        str2 = str2 + str[i];
                    }
                }
                if (str2.Length == so)
                {
                    break;
                }
            }
            return str2.PadRight(3, '0');
        }
        public void upd_donthuocct(string mmyy, bool edit,long m_id, int i_stt, long l_mabd, decimal d_soluong, string m_cachdung,int i_makho, int i_manguon)
        {
            if (edit) sql = "update " + user + ".d_donthuocct_"+mmyy+" set stt=:i_stt,makho=:i_makho,manguon=:i_manguon,mabd=:l_mabd,soluong=:d_soluong,cachdung=:m_cachdung where id=:m_id and mabd=:l_mabd and stt=:i_stt";
            else sql = "update " + user + ".d_donthuocct_"+mmyy+" set soluong=soluong-:d_soluong where id=:m_id and mabd=:l_mabd and stt=:i_stt";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("i_stt", NpgsqlDbType.Numeric).Value = i_stt;
                cmd.Parameters.Add("i_makho", NpgsqlDbType.Numeric).Value = i_makho;
                cmd.Parameters.Add("i_manguon", NpgsqlDbType.Numeric).Value = i_manguon;
                cmd.Parameters.Add("l_mabd", NpgsqlDbType.Numeric).Value = l_mabd;
                cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                cmd.Parameters.Add("m_cachdung", NpgsqlDbType.Text).Value = m_cachdung;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_donthuocct_"+mmyy+"(id,stt,makho,manguon,mabd,soluong,cachdung) values (:m_id,:i_stt,:i_makho,:i_manguon,:l_mabd,:d_soluong,:m_cachdung)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("i_stt", NpgsqlDbType.Numeric).Value = i_stt;
                    cmd.Parameters.Add("i_makho", NpgsqlDbType.Numeric).Value = i_makho;
                    cmd.Parameters.Add("i_manguon", NpgsqlDbType.Numeric).Value = i_manguon;
                    cmd.Parameters.Add("l_mabd", NpgsqlDbType.Numeric).Value = l_mabd;
                    cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                    cmd.Parameters.Add("m_cachdung", NpgsqlDbType.Text).Value = m_cachdung;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.ToString(), sComputer, "d_donthuocct_"+mmyy);
            }
            finally
            {
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        public bool upd_donthuocll(string mmyy,long l_id,int i_sophieu,string m_mabn,long m_maql,string m_ngay,string m_makp,string m_chandoan,string m_maicd,string m_mabs,string m_ghichu, int i_userid, int i_songay)
        {
            sql = "update " + user + ".d_donthuocll_"+mmyy+" set sophieu=:i_sophieu, mabn=:m_mabn, maql=:m_maql, ngay=:m_ngay, makp=:m_makp, chandoan=:m_chandoan, maicd=:m_maicd, mabs=:m_mabs, ghichu=:m_ghichu, userid=:i_userid, songay=:i_songay where id=:l_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                cmd.Parameters.Add("i_sophieu", NpgsqlDbType.Numeric).Value = i_sophieu;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                cmd.Parameters.Add("m_chandoan", NpgsqlDbType.Text).Value = m_chandoan;
                cmd.Parameters.Add("m_maicd", NpgsqlDbType.Varchar).Value = m_maicd;
                cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar).Value = m_mabs;
                cmd.Parameters.Add("m_ghichu", NpgsqlDbType.Text).Value = m_ghichu;
                cmd.Parameters.Add("i_userid", NpgsqlDbType.Numeric).Value = i_userid;
                cmd.Parameters.Add("i_songay", NpgsqlDbType.Numeric).Value = i_songay;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_donthuocll_"+mmyy+"(id, sophieu, mabn, maql, ngay, makp, chandoan, maicd, mabs, ghichu, userid, ngayud, songay) values (:l_id,:i_sophieu,:m_mabn,:m_maql,:m_ngay,:m_makp,:m_chandoan,:m_maicd,:m_mabs,:m_ghichu,:i_userid,now(),:i_songay)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                    cmd.Parameters.Add("i_sophieu", NpgsqlDbType.Numeric).Value = i_sophieu;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                    cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                    cmd.Parameters.Add("m_chandoan", NpgsqlDbType.Text).Value = m_chandoan;
                    cmd.Parameters.Add("m_maicd", NpgsqlDbType.Varchar).Value = m_maicd;
                    cmd.Parameters.Add("m_mabs", NpgsqlDbType.Varchar).Value = m_mabs;
                    cmd.Parameters.Add("m_ghichu", NpgsqlDbType.Text).Value = m_ghichu;
                    cmd.Parameters.Add("i_userid", NpgsqlDbType.Numeric).Value = i_userid;
                    cmd.Parameters.Add("i_songay", NpgsqlDbType.Numeric).Value = i_songay;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_donthuocll_" + mmyy + "_" + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_theodoisua(long l_id, string m_mabn, long m_maql, int i_mavp, string m_ten, decimal d_soluong, int i_userid, string m_loai, string m_tablename, int i_them, int i_sua, int i_huy)
        {
            try
            {
                sql = "insert into " + user + ".theodoisua(id, mabn, maql, mavp, ten, soluong, ngayud, userid, loai, tablename, them, sua, huy) values (:l_id, :m_mabn, :m_maql, :i_mavp, :m_ten, :d_soluong, now(), :i_userid, :m_loai, :m_tablename, :i_them, :i_sua, :i_huy)";
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar,8).Value = m_mabn;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("i_mavp", NpgsqlDbType.Numeric).Value = i_mavp;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                cmd.Parameters.Add("i_userid", NpgsqlDbType.Numeric).Value = i_userid;
                cmd.Parameters.Add("m_loai", NpgsqlDbType.Varchar,2).Value = m_loai;
                cmd.Parameters.Add("m_tablename", NpgsqlDbType.Varchar, 20).Value = m_tablename;
                cmd.Parameters.Add("i_them", NpgsqlDbType.Numeric).Value = i_them;
                cmd.Parameters.Add("i_sua", NpgsqlDbType.Numeric).Value = i_sua;
                cmd.Parameters.Add("i_huy", NpgsqlDbType.Numeric).Value = i_huy;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "theodoisua " + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_chidinh(long l_id, int i_stt, string m_mabn, long m_maql, string m_ngay, string m_makp,int i_madoituong, int i_mavp, decimal d_soluong, decimal d_dongia, int i_paid, int i_done, int i_userid, long l_idchidinh,string m_ghichu,string mabs)
        {
            sql = "update " + user + ".v_chidinh set mabn=:m_mabn, maql=:m_maql, ngay=:m_ngay, makp=:m_makp, madoituong=:i_madoituong, mavp=:i_mavp, soluong=:d_soluong, dongia=:d_dongia, paid=:i_paid, done=:i_done, userid=:i_userid, idchidinh=:l_idchidinh, ghichu=:m_ghichu,mabs=:mabs where id=:l_id and stt=:i_stt";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                cmd.Parameters.Add("i_stt", NpgsqlDbType.Numeric).Value = i_stt;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                cmd.Parameters.Add("i_madoituong", NpgsqlDbType.Numeric).Value = i_madoituong;
                cmd.Parameters.Add("i_mavp", NpgsqlDbType.Numeric).Value = i_mavp;
                cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                cmd.Parameters.Add("i_paid", NpgsqlDbType.Numeric).Value = i_paid;
                cmd.Parameters.Add("i_done", NpgsqlDbType.Numeric).Value = i_done;
                cmd.Parameters.Add("i_userid", NpgsqlDbType.Numeric).Value = i_userid;
                cmd.Parameters.Add("l_idchidinh", NpgsqlDbType.Numeric).Value = l_idchidinh;
                cmd.Parameters.Add("m_ghichu", NpgsqlDbType.Text).Value = m_ghichu;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".v_chidinh(id, stt, mabn, maql, ngay, makp, madoituong, mavp, soluong, dongia, paid, done, userid, ngayud, idchidinh, ghichu,mabs) values (:l_id,:i_stt,:m_mabn,:m_maql,:m_ngay,:m_makp,:i_madoituong,:i_mavp,:d_soluong,:d_dongia,:i_paid,:i_done,:i_userid,now(),:l_idchidinh,:m_ghichu,:mabs)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                    cmd.Parameters.Add("i_stt", NpgsqlDbType.Numeric).Value = i_stt;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("m_ngay", NpgsqlDbType.Timestamp).Value = StringToDateTime(m_ngay);
                    cmd.Parameters.Add("m_makp", NpgsqlDbType.Varchar, 2).Value = m_makp;
                    cmd.Parameters.Add("i_madoituong", NpgsqlDbType.Numeric).Value = i_madoituong;
                    cmd.Parameters.Add("i_mavp", NpgsqlDbType.Numeric).Value = i_mavp;
                    cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                    cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                    cmd.Parameters.Add("i_paid", NpgsqlDbType.Numeric).Value = i_paid;
                    cmd.Parameters.Add("i_done", NpgsqlDbType.Numeric).Value = i_done;
                    cmd.Parameters.Add("i_userid", NpgsqlDbType.Numeric).Value = i_userid;
                    cmd.Parameters.Add("l_idchidinh", NpgsqlDbType.Numeric).Value = l_idchidinh;
                    cmd.Parameters.Add("m_ghichu", NpgsqlDbType.Text).Value = m_ghichu;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "v_chidinh " + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public int get_sophieu(string m_ngay)
        {
            int i_sophieu = 0;
            try
            {
                sql = "select max(sophieu) as sophieu from "+user+".d_donthuocll where to_char(ngay,'dd/mm/yyyy')='" + m_ngay + "'";
                i_sophieu = int.Parse(get_data(sql).Tables[0].Rows[0]["sophieu"].ToString());
            }
            catch { }
            return i_sophieu += 1;
        }
        public void updrec_donthuocct(DataTable dt, long l_id, int i_stt, int i_mabd, string m_ma, string m_ten, string m_tenhc, string m_dang, decimal d_soluong,string m_cachdung, int i_makho, int i_manguon, DataTable dtton)
        {
            string exp = "id=" + l_id + " and mabd=" + i_mabd + " and stt=" + i_stt;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["id"] = l_id;
                nrow["stt"] = i_stt;
                nrow["mabd"] = i_mabd;
                nrow["ma"] = m_ma;
                nrow["ten"] = m_ten;
                nrow["tenhc"] = m_tenhc;
                nrow["dang"] = m_dang;
                nrow["soluong"] = d_soluong;
                nrow["cachdung"] = m_cachdung;
                nrow["makho"]=i_makho;
                nrow["manguon"]=i_manguon;
                dt.Rows.Add(nrow);
                if (dtton != null) updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, d_soluong, "-");
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                if (dtton != null)
                {
                    decimal sl = decimal.Parse(dr[0]["soluong"].ToString());
                    updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, sl, "+");
                    updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, d_soluong, "-");
                }
                dr[0]["id"] = l_id;
                dr[0]["stt"] = i_stt;
                dr[0]["mabd"] = i_mabd;
                dr[0]["ma"] = m_ma;
                dr[0]["ten"] = m_ten;
                dr[0]["tenhc"] = m_tenhc;
                dr[0]["dang"] = m_dang;
                dr[0]["soluong"] = d_soluong;
                dr[0]["cachdung"] = m_cachdung;
                dr[0]["makho"] = i_makho;
                dr[0]["manguon"] = i_manguon;
            }
            dt.AcceptChanges();
        }
        public void updrec_chidinh(DataTable dt, int i_mavp, int i_stt, string m_ma, string m_ten, string m_dvt, decimal d_soluong, decimal d_dongia)
        {
            string exp = "id=" + i_mavp;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["id"] = i_mavp;
                nrow["stt"] = i_stt;
                nrow["ma"] = m_ma;
                nrow["ten"] = m_ten;
                nrow["dvt"] = m_dvt;
                nrow["soluong"] = d_soluong;
                nrow["dongia"] = d_dongia;
                dt.Rows.Add(nrow);
            }
            dt.AcceptChanges();
        }
        public void upd_d_dmbd(long m_id, string m_ma, string m_ten, string m_dang, string m_hamluong, string m_quycach, string m_duongdung, int m_manhom, int m_mahang, int m_manuoc, string m_tenhc, string m_mahc, int m_macongty, int m_userid, int m_hide)
        {
            sql = "update " + user + ".d_dmbd set ma=:m_ma, ten=:m_ten,dang=:m_dang,hamluong=:m_hamluong,quycach=:m_quycach,duongdung=:m_duongdung,manhom=:m_manhom,mahang=:m_mahang, manuoc=:m_manuoc,tenhc=:m_tenhc, mahc=:m_mahc, madv=:m_macongty,userid=:m_userid,ngayud=now(),hide=:m_hide where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar,10).Value = m_ma;
                cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                cmd.Parameters.Add("m_dang", NpgsqlDbType.Text).Value = m_dang;
                cmd.Parameters.Add("m_hamluong", NpgsqlDbType.Varchar, 20).Value = m_hamluong;
                cmd.Parameters.Add("m_quycach", NpgsqlDbType.Text).Value = m_quycach;
                cmd.Parameters.Add("m_duongdung", NpgsqlDbType.Text).Value = m_duongdung;
                cmd.Parameters.Add("m_manhom", NpgsqlDbType.Numeric,3).Value = m_manhom;
                cmd.Parameters.Add("m_mahang", NpgsqlDbType.Numeric,5).Value = m_mahang;
                cmd.Parameters.Add("m_manuoc", NpgsqlDbType.Numeric,5).Value = m_manuoc;
                cmd.Parameters.Add("m_tenhc", NpgsqlDbType.Text).Value = m_tenhc;
                cmd.Parameters.Add("m_mahc", NpgsqlDbType.Varchar, 10).Value = m_mahc;
                cmd.Parameters.Add("m_macongty", NpgsqlDbType.Numeric,4).Value = m_macongty;
                cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric, 5).Value = m_userid;
                cmd.Parameters.Add("m_hide", NpgsqlDbType.Numeric, 1).Value = m_hide;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_dmbd(id, ma, ten, dang, hamluong, quycach,duongdung, manhom, mahang, manuoc, tenhc, mahc, madv, userid, hide) values (:m_id,:m_ma,:m_ten,:m_dang,:m_hamluong,:m_quycach,:m_duongdung,:m_manhom,:m_mahang,:m_manuoc,:m_tenhc,:m_mahc,:m_macongty,:m_userid,:m_hide)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_ma", NpgsqlDbType.Varchar, 10).Value = m_ma;
                    cmd.Parameters.Add("m_ten", NpgsqlDbType.Text).Value = m_ten;
                    cmd.Parameters.Add("m_dang", NpgsqlDbType.Text).Value = m_dang;
                    cmd.Parameters.Add("m_hamluong", NpgsqlDbType.Varchar, 20).Value = m_hamluong;
                    cmd.Parameters.Add("m_quycach", NpgsqlDbType.Text).Value = m_quycach;
                    cmd.Parameters.Add("m_duongdung", NpgsqlDbType.Text).Value = m_duongdung;
                    cmd.Parameters.Add("m_manhom", NpgsqlDbType.Numeric, 3).Value = m_manhom;
                    cmd.Parameters.Add("m_mahang", NpgsqlDbType.Numeric, 5).Value = m_mahang;
                    cmd.Parameters.Add("m_manuoc", NpgsqlDbType.Numeric, 5).Value = m_manuoc;
                    cmd.Parameters.Add("m_tenhc", NpgsqlDbType.Text).Value = m_tenhc;
                    cmd.Parameters.Add("m_mahc", NpgsqlDbType.Varchar, 10).Value = m_mahc;
                    cmd.Parameters.Add("m_macongty", NpgsqlDbType.Numeric, 4).Value = m_macongty;
                    cmd.Parameters.Add("m_userid", NpgsqlDbType.Numeric, 5).Value = m_userid;
                    cmd.Parameters.Add("m_hide", NpgsqlDbType.Numeric, 1).Value = m_hide;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_dmbd" + m_ma);
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public DataTable get_tonkhoth(string mmyy, int makho)
        {
            sql = "select a.oid as stt,c.ten tennguon,b.ma,trim(b.ten)||' '||b.hamluong as ten,b.tenhc,b.dang,d.ten as tenhang,round(a.tondau+a.slnhap-a.slxuat-case when a.slyeucau>0 then a.slyeucau else 0 end,1) as soluong,b.id,a.manguon,a.makho,b.mahc,b.donvi,e.ten as tennuoc,b.sltoithieu from "+user+".d_tonkhoth_"+mmyy+" a," + user + ".d_dmbd b," + user + ".d_dmnguon c," + user + ".d_dmhang d," + user + ".d_dmnuoc e where a.mabd=b.id and a.manguon=c.id and b.mahang=d.id and b.manuoc=e.id  and a.makho=" + makho + " and a.mmyy='" + mmyy + "'";
            sql += " order by b.ten";
            DataTable tmp = get_data(sql).Tables[0];
            string mmyys = "";
            DataRow r1;
            foreach (DataRow r2 in get_data("select mmyy from "+user+".table where substr(mmyy,3,2)||substr(mmyy,1,2)>'" + mmyy.Substring(2, 2) + mmyy.Substring(0, 2) + "'").Tables[0].Rows)
            {
                mmyys = r2["mmyy"].ToString();
                foreach (DataRow r in get_data("select mabd,manguon,sum(slxuat+case when slyeucau>0 then slyeucau else 0 end) as soluong from "+user+".d_tonkhoth_"+mmyys+" where slnhap=0 and slxuat+case when slyeucau>0 then slyeucau else 0 end>0 and makho=" + makho + " and mmyy='" + mmyys + "' group by mabd,manguon").Tables[0].Rows)
                {
                    r1 = getrowbyid(tmp, "id=" + int.Parse(r["mabd"].ToString()) + " and manguon=" + int.Parse(r["manguon"].ToString()));
                    if (r1 != null) r1["soluong"] = decimal.Parse(r1["soluong"].ToString()) - decimal.Parse(r["soluong"].ToString());
                }
            }
            return tmp;
        }
        public DataTable get_tonkhoth(string mmyy, string makho, string manguon)
        {
            sql = "select id from " + user + ".d_dmkho where id>0";
            if (makho != "") sql += " and id in (" + makho + ")";
            foreach (DataRow r in get_data(sql).Tables[0].Rows)
                upd_tonkho_th(mmyy, int.Parse(r["id"].ToString()));
            sql = "select a.oid as stt,d.ten as tennguon,b.ma,trim(b.ten)||' '||b.hamluong as ten,b.tenhc,b.dang,";//a.oid
            sql+= "c.ten as tenkho,round(a.tondau+a.slnhap-a.slxuat-case when a.slyeucau>0 then a.slyeucau else 0 end,1) as soluong,";
            sql+= "round(b.giaban,2) as giaban,";
            sql+= "b.id,a.makho,a.manguon,b.mahc from " + user + ".d_tonkhoth_"+mmyy+" a," + user + ".d_dmbd b," + user + ".d_dmkho c," + user + ".d_dmnguon d where a.mabd=b.id and a.makho=c.id and a.manguon=d.id";
            if (makho != "") sql += " and a.makho in (" + makho + ")";
            if (manguon != "") sql += " and a.manguon in (" + manguon + ")";
            sql += " order by b.ten";
            DataTable tmp = get_data(sql).Tables[0];
            DataRow r1;
            string mmyys = "";
            foreach (DataRow r2 in get_data("select mmyy from " + user + ".table where substr(mmyy,3,2)||substr(mmyy,1,2)>'" + mmyy.Substring(2, 2) + mmyy.Substring(0, 2) + "'").Tables[0].Rows)
            {
                mmyys = r2["mmyy"].ToString();
                sql = "select makho,mabd,manguon,sum(slxuat+case when slyeucau>0 then slyeucau else 0 end) as soluong from " + user + mmyys + ".d_tonkhoth where slxuat+slyeucau>0";
                if (makho != "") sql += " and makho in (" + makho + ")";
                if (manguon != "") sql += " and manguon in (" + manguon + ")";
                sql += " group by makho,mabd,manguon";
                foreach (DataRow r in get_data(sql).Tables[0].Rows)
                {
                    r1 = getrowbyid(tmp, "makho=" + int.Parse(r["makho"].ToString()) + " and id=" + int.Parse(r["mabd"].ToString()) + " and manguon=" + int.Parse(r["manguon"].ToString()));
                    if (r1 != null) r1["soluong"] = decimal.Parse(r1["soluong"].ToString()) - decimal.Parse(r["soluong"].ToString());
                }
            }
            return tmp;
        }
        public decimal get_slton_nguon(DataTable dt, int makho, int mabd, int manguon, decimal soluong)
        {
            DataRow r = getrowbyid(dt, "makho=" + makho + " and id=" + mabd + " and manguon=" + manguon);
            if (r == null) return 0;
            else return decimal.Parse(r["soluong"].ToString()) + soluong;
        }
        public DataTable get_tonkhoct(string mmyy, string makho, string manguon)
        {
            sql = "select a.*,b.ten as tennguon,c.ten as tennhomcc from " + user + ".d_tonkhoct_"+mmyy+" a," + user + ".d_dmnguon b," + user + ".d_dmnx c where a.manguon=b.id and a.nhomcc=c.id";
            if (makho != "") sql += " and a.makho in (" + makho.Substring(0, makho.Length - 1) + ")";
            if (manguon != "") sql += " and a.manguon in (" + manguon + ")";
            sql += " order by a.stt";
            DataTable tmp = get_data(sql).Tables[0];
            DataRow r1;
            string mmyys = "";
            foreach (DataRow r2 in get_data("select mmyy from " + user + ".d_table where substr(mmyy,3,2)||substr(mmyy,1,2)>'" + mmyy.Substring(2, 2) + mmyy.Substring(0, 2) + "'").Tables[0].Rows)
            {
                mmyys = r2["mmyy"].ToString();
                sql = "select a.makho,a.stt,sum(a.slxuat) as soluong from " + user + ".d_tonkhoct_"+mmyys+" a," + user + ".d_dmkho d where a.makho=d.id and a.slxuat>0";
                if (makho != "") sql += " and a.makho in (" + makho.Substring(0, makho.Length - 1) + ")";
                if (manguon != "") sql += " and a.manguon in (" + manguon + ")";
                sql += " group by a.makho,a.stt";
                foreach (DataRow r in get_data(sql).Tables[0].Rows)
                {
                    r1 = getrowbyid(tmp, "makho=" + int.Parse(r["makho"].ToString()) + " and stt=" + long.Parse(r["stt"].ToString()));
                    if (r1 != null) r1["slxuat"] = decimal.Parse(r1["slxuat"].ToString()) + decimal.Parse(r["soluong"].ToString());
                }
            }
            return tmp;
        }
        public bool upd_tonkho_th(string s_mmyy, int d_makho)
        {
            sql = user + ".upd_tonkho_" + s_mmyy;
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("d_makho", NpgsqlDbType.Numeric).Value = d_makho;
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "tonkho");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_theodoigia(int d_mabd, string d_ngay, decimal d_dongia)
        {
            sql = "update " + user + ".d_theodoigia set dongia=:d_dongia,ngayud=now() where mabd=:d_mabd and to_char(ngay,'dd/mm/yyyy')=:d_ngay";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                cmd.Parameters.Add("d_ngay", NpgsqlDbType.Varchar, 10).Value = d_ngay;
                cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into "+user+".d_theodoigia (mabd,ngay,dongia,ngayud) values(:d_mabd,:d_ngay,:d_dongia,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                    cmd.Parameters.Add("d_ngay", NpgsqlDbType.Varchar, 10).Value = d_ngay;
                    cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose(); 
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_theodoigia");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_dienthoai(string mabn, string dienthoai, string didong, string coquan)
        {
            sql = "update " + user + ".dienthoai set dienthoai=:dienthoai, didong=:didong, coquan=:coquan where mabn=:mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("mabn", NpgsqlDbType.Varchar).Value = mabn;
                cmd.Parameters.Add("dienthoai", NpgsqlDbType.Varchar).Value = dienthoai;
                cmd.Parameters.Add("didong", NpgsqlDbType.Varchar).Value = didong;
                cmd.Parameters.Add("coquan", NpgsqlDbType.Varchar).Value = coquan;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dienthoai (mabn,dienthoai,didong,coquan) values(:mabn,:dienthoai,:didong,:coquan)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("mabn", NpgsqlDbType.Varchar).Value = mabn;
                    cmd.Parameters.Add("dienthoai", NpgsqlDbType.Varchar).Value = dienthoai;
                    cmd.Parameters.Add("didong", NpgsqlDbType.Varchar).Value = didong;
                    cmd.Parameters.Add("coquan", NpgsqlDbType.Varchar).Value = coquan;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "dienthoai_"+mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_dlogin(int i_id,string hoten, string userid, string pass, string menu, string makp, int sua, int huy)
        {
            sql = "update " + user + ".dlogin set hoten=:hoten, userid=:userid, password=:pass, menu=:menu, makp=:makp, sua=:sua, huy=:huy, ngayud=now() where id=:i_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("i_id", NpgsqlDbType.Numeric).Value = i_id;
                cmd.Parameters.Add("hoten", NpgsqlDbType.Text).Value = hoten;
                cmd.Parameters.Add("userid", NpgsqlDbType.Varchar).Value = userid;
                cmd.Parameters.Add("pass", NpgsqlDbType.Varchar).Value = pass;
                cmd.Parameters.Add("menu", NpgsqlDbType.Varchar).Value = menu;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("sua", NpgsqlDbType.Numeric).Value = sua;
                cmd.Parameters.Add("huy", NpgsqlDbType.Numeric).Value = huy;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".dlogin (id,hoten,userid,password,menu,makp,sua,huy,ngayud) values(:i_id,:hoten,:userid,:pass,:menu,:makp,:sua,:huy,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("i_id", NpgsqlDbType.Numeric).Value = i_id;
                    cmd.Parameters.Add("hoten", NpgsqlDbType.Text).Value = hoten;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Varchar).Value = userid;
                    cmd.Parameters.Add("pass", NpgsqlDbType.Varchar).Value = pass;
                    cmd.Parameters.Add("menu", NpgsqlDbType.Varchar).Value = menu;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("sua", NpgsqlDbType.Numeric).Value = sua;
                    cmd.Parameters.Add("huy", NpgsqlDbType.Numeric).Value = huy;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "dlogin");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_khoaso(string d_mmyy, int d_userid)
        {
            sql = "update " + user + ".d_khoaso set userid=:d_userid,ngayud=now() where mmyy=:d_mmyy";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                cmd.Parameters.Add("d_mmyy", NpgsqlDbType.Varchar, 4).Value = d_mmyy;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_khoaso (mmyy,userid,ngayud) values (:d_mmyy,:d_userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("d_mmyy", NpgsqlDbType.Varchar, 4).Value = d_mmyy;
                    cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_khoaso");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_table(string d_mmyy, int d_userid)
        {
            sql = "update " + user + ".table set userid=:d_userid,ngayud=now(),computer=:m_computer where mmyy=:d_mmyy";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar).Value = sComputer;
                cmd.Parameters.Add("d_mmyy", NpgsqlDbType.Varchar, 4).Value = d_mmyy;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".table (mmyy,userid,ngayud,computer,bak) values (:d_mmyy,:d_userid,now(),:m_computer,0)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                    cmd.Parameters.Add("m_computer", NpgsqlDbType.Varchar).Value = sComputer;
                    cmd.Parameters.Add("d_mmyy", NpgsqlDbType.Varchar, 4).Value = d_mmyy;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "table");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool bMmyy(string mmyy)
        {
            return get_data("select mmyy from " + user + ".table where mmyy='" + mmyy + "'").Tables[0].Rows.Count > 0;
        }
        public bool bKhoaso(string mmyy)
        {
            return get_data("select mmyy from " + user + ".d_khoaso where mmyy='" + mmyy + "'").Tables[0].Rows.Count > 0;
        }
        public void tao_table(string mmyy)
        {
            sql="CREATE TABLE qlpk.d_donthuocll_"+mmyy+"(id numeric(18) NOT NULL DEFAULT 0,  sophieu numeric(5) DEFAULT 0,  mabn varchar(8),  maql numeric(18) DEFAULT 0,";
            sql+="ngay timestamp,  makp varchar(2),  chandoan text,  maicd varchar(9),  mabs varchar(4),  ghichu text,  userid numeric(5) DEFAULT 0,  ngayud timestamp DEFAULT now(),";
            sql+=" done numeric(1) DEFAULT 0,  songay numeric(2) DEFAULT 1,  CONSTRAINT pk_d_donthuocll_mmyy PRIMARY KEY (id),";
            sql += " CONSTRAINT fk_d_donthuocll_btdbn_" + mmyy + " FOREIGN KEY (mabn) REFERENCES qlpk.btdbn (mabn) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL)WITH OIDS;";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_donthuocct_" + mmyy + "(id numeric(18) NOT NULL DEFAULT 0,  stt numeric(3) NOT NULL DEFAULT 0, makho numeric(3),manguon numeric(2), mabd numeric(7) DEFAULT 0,";
            sql += "soluong numeric(10,2) DEFAULT 0,  cachdung text, CONSTRAINT pk_d_donthuocct_" + mmyy + " PRIMARY KEY (id, stt), ";
            sql += "CONSTRAINT fk_d_donthuocct_d_donthuocll_" + mmyy + " FOREIGN KEY (id) REFERENCES qlpk.d_donthuocll_"+mmyy+" (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL) WITH OIDS;";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_toathuocll_" + mmyy + "(id numeric(18) NOT NULL DEFAULT 0,mabn varchar(8),maql ";
            sql+="numeric(18) DEFAULT 0,hoten text,namsinh varchar(4),phai numeric(1),ngay timestamp,mabs varchar(4),makp ";
            sql += "varchar(2),maicd varchar(9),chandoan text,userid numeric(5),ngayud timestamp DEFAULT now(),lanin numeric(2) DEFAULT 0,sotoa numeric(7) DEFAULT 0,idduyet numeric(18) DEFAULT 0,CONSTRAINT pk_d_toathuocll_" + mmyy + " PRIMARY KEY (id) USING INDEX TABLESPACE qlpk_index) WITH OIDS;  ";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_toathuocct_" + mmyy + "(id numeric(18) NOT NULL DEFAULT 0,stt numeric(3) NOT NULL DEFAULT 0,sttt numeric(18) DEFAULT 0,manguon numeric(2), makho numeric(3) DEFAULT 0, madv numeric(5),mabd numeric(7) DEFAULT ";
            sql += "0,handung varchar(10), losx varchar(20),soluong numeric(10,2) DEFAULT 0,giaban numeric(18,4) DEFAULT 0,giamua numeric(18,4) DEFAULT 0,CONSTRAINT pk_d_toathuocct_" + mmyy + " PRIMARY KEY (id, stt) USING INDEX TABLESPACE qlpk_index)WITH OIDS;";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_tonkhoct_" + mmyy + "(makho numeric(3),manguon numeric(2),stt numeric(18),idn numeric(18),sttn numeric(3),mabd numeric(7),handung varchar(10), losx varchar(20),madv numeric(5),tondau  numeric(12,4),slnhap  numeric(12,4),slxuat  numeric(12,4),giamua  numeric(24,10),giaban  numeric(15,4),";
            sql += "CONSTRAINT fk_d_tonkhoct_d_dmbd_" + mmyy + " FOREIGN KEY (mabd) REFERENCES qlpk.d_dmbd(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT fk_d_tonkhoct_d_dmkho_" + mmyy + " FOREIGN KEY (makho) REFERENCES qlpk.d_dmkho(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT pk_d_tonkhoct_" + mmyy + " PRIMARY KEY (makho, stt)) WITH OIDS;";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_tonkhoth_" + mmyy + "(makho numeric(3),mabd numeric(7),tondau numeric(12,4),slnhap numeric(12,4),slxuat numeric(12,4),slyeucau  numeric(12,4),manguon numeric(2),";
            sql += "CONSTRAINT pk_d_tonkhoth_" + mmyy + " PRIMARY KEY (mabd, makho, manguon),";
            sql += "CONSTRAINT fk_d_tonkhoth_d_dmbd_" + mmyy + " FOREIGN KEY (mabd) REFERENCES qlpk.d_dmbd(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT fk_d_tonkhoth_d_dmkho_" + mmyy + " FOREIGN KEY (makho) REFERENCES qlpk.d_dmkho(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT fk_d_tonkhoth_d_dmnguon_" + mmyy + " FOREIGN KEY (manguon) REFERENCES qlpk.d_dmnguon(id) ON DELETE SET NULL) WITH OIDS;";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_nhapll_" + mmyy + "(id numeric(18,0),sophieu character varying(10),ngaysp timestamp without time zone,";
            sql+="sohd text,ngayhd timestamp without time zone,loai character varying(1),nguoigiao character varying(30),madv numeric(4,0),";
            sql+="makho numeric(3,0),manguon numeric(2,0),no text,co character varying(10),userid numeric(5,0),ngayud timestamp without time zone,";
            sql += "chietkhau  numeric(5,2),CONSTRAINT fk_d_nhapll_d_dmkho_" + mmyy + " FOREIGN KEY (makho) REFERENCES qlpk.d_dmkho(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT fk_d_nhapll_d_dmnguon_" + mmyy + " FOREIGN KEY (manguon) REFERENCES qlpk.d_dmnguon(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT pk_d_nhapll_" + mmyy + " PRIMARY KEY (id));";
            execute_data(sql);
            sql = "CREATE TABLE qlpk.d_nhapct_" + mmyy + "(id numeric(18),stt numeric(3),mabd numeric(7),madv numeric(5),handung varchar(10),losx varchar(20),vat numeric(3),soluong numeric(12,4),";
            sql+="dongia numeric(24,10),sotien numeric(15,4),giaban numeric(18),giamua numeric(24,10),sl1 numeric(12,4),sl2 numeric(5),tyle numeric(7,2),";
            sql+="giabancu numeric(18),giamuacu numeric(24,10),tyle_ggia numeric(5,2),st_ggia numeric(24,10),";
            sql += "CONSTRAINT fk_d_nhapct_d_dmbd_" + mmyy + " FOREIGN KEY (mabd) REFERENCES qlpk.d_dmbd(id) ON DELETE SET NULL,";
            sql += "CONSTRAINT pk_d_nhapct_" + mmyy + " PRIMARY KEY (id, stt),";
            sql += "CONSTRAINT fk_d_nhapct_d_nhapll_" + mmyy + " FOREIGN KEY (id) REFERENCES qlpk.d_nhapll_"+mmyy+"(id) ON DELETE SET NULL);";
            execute_data(sql);
            sql = "CREATE INDEX tonkhoct_mabd_"+mmyy+" ON qlpk.d_tonkhoct_"+mmyy+" USING btree (mabd);";
            execute_data(sql);
            sql = "CREATE INDEX tonkhoct_stt_" + mmyy + " ON qlpk.d_tonkhoct_" + mmyy + " USING btree (stt);";
            execute_data(sql);
        }
        public void tao_function(string mmyy)
        {
            sql="CREATE OR REPLACE FUNCTION qlpk.upd_tonkho_"+mmyy+" (d_makho NUMERIC)\n";
            sql+=" RETURNS void AS $$ \n";
            sql+=" DECLARE\n";
            sql+=" d_makho numeric;d_mabd numeric;d_tondau numeric;d_slnhap numeric;d_slxuat numeric;d_manguon numeric;\n";
            sql+=" cur_tonkho cursor  is select a.makho,a.mabd,sum(a.tondau) as tondau,sum(a.slnhap) as slnhap,sum(a.slxuat) as slxuat,a.manguon from qlpk.d_tonkhoct_"+mmyy+" a where a.makho in (d_makho) group by a.makho,a.mabd,a.manguon;\n";
            sql+=" begin\n";
            sql+="    update qlpk.d_tonkhoth_"+mmyy+" set tondau=0,slnhap=0,slxuat=0 where makho in (d_makho);\n";
            sql+="    open cur_tonkho;\n";
            sql+="      loop\n";
            sql+="          fetch cur_tonkho into d_makho,d_mabd,d_tondau,d_slnhap,d_slxuat,d_manguon;\n";
            sql+="              if found=false then\n";
            sql+="                  exit;\n";
            sql+="              end if;\n";
            sql+="                  update qlpk.d_tonkhoth_"+mmyy+" set tondau=tondau+d_tondau,slnhap=slnhap+d_slnhap,slxuat=slxuat+d_slxuat where makho=d_makho and mabd=d_mabd and manguon=d_manguon;\n";
            sql+="              if found=false then\n";
            sql+="                  insert into qlpk.d_tonkhoth_"+mmyy+"(makho,mabd,tondau,slnhap,slxuat,manguon) values (d_makho,d_mabd,d_tondau,d_slnhap,d_slxuat,d_manguon);\n";
            sql+="              end if;\n";
            sql+="      end loop;\n";
            sql+="  close cur_tonkho;\n";
            sql+=" end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'\n";
            execute_data(sql);
            //
            sql = "CREATE OR REPLACE FUNCTION qlpk.upd_tonkhoct_" + mmyy + "(\n";
            sql+=" d_makho NUMERIC,\n";
            sql+=" d_stt NUMERIC,\n";
            sql+=" d_mabd NUMERIC,\n"; 
            sql+=" d_tondau NUMERIC\n";
            sql+=" )\n";
            sql+=" RETURNS void AS $$\n";
            sql+="  begin\n";
            sql+="      update qlpk.d_tonkhoct_"+mmyy+" set mabd=d_mabd,tondau=d_tondau where makho=d_makho and stt=d_stt;\n";
            sql+="      if found=false then\n";
            sql+="          insert into qlpk.d_tonkhoct_"+mmyy+"(makho,stt,idn,sttn,mabd,tondau,slnhap,slxuat) values (d_makho,d_stt,0,0,d_mabd,d_tondau,0,0);\n";
            sql+="      end if;\n";
            sql+="  end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'\n";
            execute_data(sql);
            //
            sql = " CREATE OR REPLACE FUNCTION qlpk.upd_toathuocll_" + mmyy + " (\n";
            sql+=" d_id NUMERIC,\n"; 
            sql+=" d_mabn VARCHAR,\n"; 
            sql+=" d_maql NUMERIC,\n";
            sql+=" d_hoten TEXT,\n"; 
            sql+=" d_namsinh VARCHAR,\n";
            sql+=" d_phai NUMERIC,\n";
            sql+=" d_ngay VARCHAR,\n"; 
            sql+=" d_mabs VARCHAR,\n"; 
            sql+=" d_makp VARCHAR,\n";
            sql+=" d_maicd VARCHAR,\n";
            sql+=" d_chandoan TEXT,\n"; 
            sql+=" d_userid NUMERIC,\n"; 
            sql+=" d_sotoa NUMERIC,\n"; 
            sql+=" d_idduyet NUMERIC\n";
            sql+=" )\n";
            sql+=" RETURNS void AS $$\n"; 
            sql+="  begin\n";
            sql+="       update qlpk.d_toathuocll_" + mmyy + " set mabn=d_mabn,maql=d_maql,hoten=d_hoten,namsinh=d_namsinh,phai=d_phai,ngay=to_timestamp(d_ngay,'dd/mm/yyyy'),mabs=d_mabs,makp=d_makp,maicd=d_maicd,chandoan=d_chandoan,userid=d_userid,sotoa=d_sotoa,idduyet=d_idduyet where id=d_id;\n";
            sql+="          if found=false then\n";
            sql += "              insert into qlpk.d_toathuocll_" + mmyy + "(id,mabn,maql,hoten,namsinh,phai,ngay,mabs,makp,maicd,chandoan,userid,sotoa,idduyet) values (d_id,d_mabn,d_maql,d_hoten,d_namsinh,d_phai,to_timestamp(d_ngay,'dd/mm/yyyy'),d_mabs,d_makp,d_maicd,d_chandoan,d_userid,d_sotoa,d_idduyet);\n";
            sql+="          end if;\n";
            sql+="  end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'\n";
            execute_data(sql);
            //
            sql = "CREATE OR REPLACE FUNCTION qlpk.upd_toathuocct_" + mmyy + " (\n";
            sql+=" d_id NUMERIC,\n"; 
            sql+=" d_stt NUMERIC,\n"; 
            sql+=" d_sttt NUMERIC,\n"; 
            sql+=" d_manguon NUMERIC,\n"; 
            sql+=" d_makho NUMERIC,\n";
            sql+=" d_madv NUMERIC,\n";
            sql+=" d_mabd NUMERIC,\n"; 
            sql+=" d_handung VARCHAR,\n";
            sql+=" d_losx VARCHAR,\n";
            sql+=" d_soluong NUMERIC,\n"; 
            sql+=" d_giaban NUMERIC,\n";
            sql+=" d_giamua NUMERIC\n";
            sql+=" )\n";
            sql+=" RETURNS void AS $$\n"; 
            sql+="  begin\n";
            sql+="      update qlpk.d_toathuocct_"+mmyy+" set sttt=d_sttt,manguon=d_manguon,makho=d_makho,madv=d_madv,mabd=d_mabd,handung=d_handung,losx=d_losx,soluong=d_soluong,giaban=d_giaban,giamua=d_giamua where id=d_id and stt=d_stt;\n";
            sql+="          if found=false then\n";
            sql+="              insert into qlpk.d_toathuocct_"+mmyy+"(id,stt,sttt,manguon,makho,madv,mabd,handung,losx,soluong,giaban,giamua) values (d_id,d_stt,d_sttt,d_manguon,d_makho,d_madv,d_mabd,d_handung,d_losx,d_soluong,d_giaban,d_giamua);\n";
            sql+="          end if;\n";
            sql+="  end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'";
            execute_data(sql);
            //
            sql = "CREATE OR REPLACE FUNCTION qlpk.upd_nhapll_" + mmyy + "(\n";
            sql+=" d_id NUMERIC,\n"; 
            sql+=" d_sophieu VARCHAR,\n"; 
            sql+=" d_ngaysp VARCHAR,\n"; 
            sql+=" d_sohd TEXT,\n"; 
            sql+=" d_ngayhd VARCHAR,\n"; 
            sql+=" d_loai VARCHAR,\n"; 
            sql+=" d_nguoigiao TEXT,\n"; 
            sql+=" d_madv NUMERIC,\n"; 
            sql+=" d_makho NUMERIC,\n"; 
            sql+=" d_manguon NUMERIC,\n"; 
            sql+=" d_no TEXT,\n"; 
            sql+=" d_co VARCHAR,\n"; 
            sql+=" d_userid NUMERIC,\n"; 
            sql+=" d_chietkhau NUMERIC\n";
            sql+=" )\n";
            sql+=" RETURNS void AS $$\n"; 
            sql+="   begin\n";
            sql+="      update qlpk.d_nhapll_"+mmyy+" set sophieu=d_sophieu,ngaysp=to_timestamp(d_ngaysp,'dd/mm/yyyy'),sohd=d_sohd,ngayhd=to_timestamp(d_ngayhd,'dd/mm/yyyy'),loai=d_loai,nguoigiao=d_nguoigiao,madv=d_madv,makho=d_makho,manguon=d_manguon,no=d_no,co=d_co,userid=d_userid,ngayud=now(),chietkhau=d_chietkhau where id=d_id;\n";
            sql+="      if found=false then\n";
            sql+="          insert into qlpk.d_nhapll_"+mmyy+"(id,sophieu,ngaysp,sohd,ngayhd,loai,nguoigiao,madv,makho,manguon,no,co,userid,ngayud,chietkhau) values (d_id,d_sophieu,to_timestamp(d_ngaysp,'dd/mm/yyyy'),d_sohd,to_timestamp(d_ngayhd,'dd/mm/yyyy'),d_loai,d_nguoigiao,d_madv,d_makho,d_manguon,d_no,d_co,d_userid,now(),d_chietkhau);\n";
            sql+="      end if;\n";
            sql+="   end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'";
            execute_data(sql);
            //
            sql = " CREATE OR REPLACE FUNCTION qlpk.upd_nhapct_" + mmyy + " (\n";
            sql+=" d_id NUMERIC,\n"; 
            sql+=" d_stt NUMERIC,\n"; 
            sql+=" d_mabd NUMERIC,\n";
            sql+=" d_madv NUMERIC,\n"; 
            sql+=" d_handung VARCHAR,\n"; 
            sql+=" d_losx VARCHAR,\n"; 
            sql+=" d_vat NUMERIC,\n"; 
            sql+=" d_soluong NUMERIC,\n"; 
            sql+=" d_dongia NUMERIC,\n"; 
            sql+=" d_sotien NUMERIC,\n"; 
            sql+=" d_giaban NUMERIC,\n"; 
            sql+=" d_giamua NUMERIC,\n"; 
            sql+=" d_sl1 NUMERIC,\n"; 
            sql+=" d_sl2 NUMERIC,\n"; 
            sql+=" d_tyle NUMERIC,\n"; 
            sql+=" d_giabancu NUMERIC,\n"; 
            sql+=" d_giamuacu NUMERIC,\n"; 
            sql+=" d_tyle_ggia NUMERIC,\n"; 
            sql+=" d_st_ggia NUMERIC\n";
            sql+=" )\n";
            sql+=" RETURNS void AS $$\n"; 
            sql+="  begin\n";
            sql+="      update qlpk.d_nhapct_"+mmyy+" set mabd=d_mabd,madv=d_madv,handung=d_handung,losx=d_losx,vat=d_vat,soluong=d_soluong,dongia=d_dongia,sotien=d_sotien,giaban=d_giaban,giamua=d_giamua,sl1=d_sl1,sl2=d_sl2,tyle=d_tyle,giabancu=d_giabancu,giamuacu=d_giamuacu,tyle_ggia=d_tyle_ggia,st_ggia=d_st_ggia where id=d_id and stt=d_stt;\n";
            sql+="      if found=false then\n";
            sql+="          insert into qlpk.d_nhapct_"+mmyy+"(id,stt,mabd,madv,handung,losx,vat,soluong,dongia,sotien,giaban,giamua,sl1,sl2,tyle,giabancu,giamuacu,tyle_ggia,st_ggia) values (d_id,d_stt,d_mabd,d_madv,d_handung,d_losx,d_vat,d_soluong,d_dongia,d_sotien,d_giaban,d_giamua,d_sl1,d_sl2,d_tyle,d_giabancu,d_giamuacu,d_tyle_ggia,d_st_ggia);\n";
            sql+="      end if;\n";
            sql+="  end;\n";
            sql+=" $$ LANGUAGE 'plpgsql'";
            execute_data(sql);
        }
        public bool upd_toathuocll(string d_mmyy, long d_id, string d_mabn, long d_maql, string d_hoten, string d_namsinh,int d_phai, string d_ngay, string d_mabs, string d_makp,string d_maicd,string d_chandoan, int d_userid, int d_sotoa, long d_idduyet)
        {
            sql = user + ".upd_toathuocll_"+d_mmyy;
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("d_id", NpgsqlDbType.Numeric).Value = d_id;
                cmd.Parameters.Add("d_mabn", NpgsqlDbType.Varchar, 8).Value = d_mabn;
                cmd.Parameters.Add("d_maql", NpgsqlDbType.Numeric).Value = d_maql;
                cmd.Parameters.Add("d_hoten", NpgsqlDbType.Text).Value = d_hoten;
                cmd.Parameters.Add("d_namsinh", NpgsqlDbType.Varchar, 4).Value = d_namsinh;
                cmd.Parameters.Add("d_phai", NpgsqlDbType.Numeric).Value = d_phai;
                cmd.Parameters.Add("d_ngay", NpgsqlDbType.Varchar, 10).Value = d_ngay;
                cmd.Parameters.Add("d_mabs", NpgsqlDbType.Varchar, 4).Value = d_mabs;
                cmd.Parameters.Add("d_makp", NpgsqlDbType.Varchar).Value = d_makp;
                cmd.Parameters.Add("d_maicd", NpgsqlDbType.Varchar).Value = d_maicd;
                cmd.Parameters.Add("d_chandoan", NpgsqlDbType.Text).Value = d_chandoan;
                cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                cmd.Parameters.Add("d_sotoa", NpgsqlDbType.Numeric).Value = d_sotoa;
                cmd.Parameters.Add("d_idduyet", NpgsqlDbType.Numeric).Value = d_idduyet;
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_toathuocll_"+d_mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_toathuocct(string d_mmyy, long d_id, long d_stt, long d_sttt, int d_manguon, int d_makho, int d_madv, int d_mabd, string d_handung, string d_losx, decimal d_soluong, decimal d_giaban,decimal d_giamua)
        {
            sql = user + ".upd_toathuocct_"+d_mmyy;
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("d_id", NpgsqlDbType.Numeric).Value = d_id;
                cmd.Parameters.Add("d_stt", NpgsqlDbType.Numeric).Value = d_stt;
                cmd.Parameters.Add("d_sttt", NpgsqlDbType.Numeric).Value = d_sttt;
                cmd.Parameters.Add("d_manguon", NpgsqlDbType.Numeric).Value = d_manguon;
                cmd.Parameters.Add("d_makho", NpgsqlDbType.Numeric).Value = d_makho;
                cmd.Parameters.Add("d_madv", NpgsqlDbType.Numeric).Value = d_madv;
                cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                cmd.Parameters.Add("d_handung", NpgsqlDbType.Varchar).Value = d_handung;
                cmd.Parameters.Add("d_losx", NpgsqlDbType.Varchar).Value = d_losx;
                cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                cmd.Parameters.Add("d_giaban", NpgsqlDbType.Numeric).Value = d_giaban;
                cmd.Parameters.Add("d_giamua", NpgsqlDbType.Numeric).Value = d_giamua;
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_toathuocct_"+d_mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public int get_sotoa_xuatban(string mmyy, string ngay)
        {
            ds = get_data("select sotoa from " + user + ".d_capsotoa where id=1 and to_char(ngay,'dd/mm/yyyy')='" + ngay + "' and mmyy='"+mmyy+"'");
            if (ds.Tables[0].Rows.Count <= 0) return 1;
            else return int.Parse(ds.Tables[0].Rows[0]["sotoa"].ToString()) + 1;
        }
        public long get_sobienlai()
        {
            ds = get_data("select sobienlai from " + user + ".v_vienphill order by sobienlai desc");
            if (ds.Tables[0].Rows.Count <= 0) return 1;
            else return long.Parse(ds.Tables[0].Rows[0]["sobienlai"].ToString()) + 1;
        }
        public long get_sobienlai_thuvp(long sobienlai)
        {
            long sbl = sobienlai;
            DataTable dt = get_data("select sobienlai from " + user + ".v_vienphill where sobienlai=" + sobienlai).Tables[0];
            if (dt.Rows.Count > 0)
            {
                sbl = get_sobienlai();
            }
            return sbl;
        }
        public int get_sotoa_xuatban(string mmyy, long id, string ngay)
        {
            DataTable dt = get_data("select sotoa from " + user + ".d_toathuocll_"+mmyy+" where sotoa<>0 and id=" + id).Tables[0];
            if (dt.Rows.Count > 0) return int.Parse(dt.Rows[0]["sotoa"].ToString());
            else
            {
                sql = "update " + user + ".d_capsotoa set sotoa=sotoa+1 where id=1 and to_char(ngay,'dd/mm/yyyy')='" + ngay + "' and mmyy='"+mmyy+"'";
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                int i= cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (i == 0)
                {
                    sql = "insert into " + user + ".d_capsotoa(id,ngay,sotoa,userid,mmyy) values(1,to_date('" + ngay + "','dd/mm/yyyy'),1,1,'" + mmyy + "')";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                //
                sql = "select sotoa from " + user + ".d_capsotoa where id=1 and to_char(ngay,'dd/mm/yyyy')='" + ngay + "' and mmyy='"+mmyy+"'";
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                ada = new NpgsqlDataAdapter(cmd);
                ds = new DataSet();
                ada.Fill(ds);
                cmd.Dispose();
                //
                con.Close(); con.Dispose();
                return int.Parse(ds.Tables[0].Rows[0]["sotoa"].ToString());
            }
        }
        public void upd_tonkhoct_xuat(string tt, string mmyy, long stt, int makho, int manguon, int mabd, string handung, string losx, int madv, decimal soluong, decimal sotien, decimal giaban, decimal giamua)
        {
            if (tt == "delete")
                execute_data("update " + user + mmyy + ".d_tonkhoct set slxuat=slxuat-" + soluong + " where makho=" + makho + " and stt=" + stt);
            else if (tt == "insert")
                upd_tonkhoct(mmyy, manguon, makho, stt, mabd, handung, losx, madv, soluong, giaban,giamua, "slxuat");
            upd_tonkhoth(tt, mmyy, makho, mabd, manguon, soluong, "slxuat");
        }
        public void upd_tonkhoct_nhapct(string tt, string mmyy, string ngay, long idn, int sttn, decimal soluong, decimal sotien, int vat, int makho, int manguon, int mabd, string handung, string losx, int madv, string loai, decimal giaban,decimal dongia, decimal sotienvat, decimal chietkhau)
        {
            if (tt == "delete")
            {
                execute_data("update " + user + ".d_tonkhoct_"+ mmyy+" set slnhap=slnhap-" + soluong + " where idn=" + idn + " and sttn=" + sttn + " and makho=" + makho);
            }
            else if (tt == "insert")
            {
                upd_tonkhoct(mmyy, makho, manguon, idn, sttn, mabd, handung, losx, madv, soluong, giaban, (sotien + sotien * vat / 100) / soluong,"slnhap");
                if (loai == "M")
                {
                    if (giaban > 0) upd_theodoigia(mabd, ngay, (sotien + sotien * vat / 100) / soluong);
                }
                if (giaban > 0 && sotien != 0) execute_data("update " + user + ".d_dmbd set dongia=" + (sotien + sotien * vat / 100 ) / soluong + ",giaban=" + giaban + " where id=" + mabd);
            }
            upd_tonkhoth(tt, mmyy, makho, mabd, manguon, soluong, "slnhap");
        }
        public void upd_tonkhoct(string mmyy, int makho, int manguon, long idn, int sttn, int mabd, string handung, string losx, int madv, decimal soluong, decimal giaban, decimal giamua, string fie1)
        {
            long stt = 0;
            sql = "update " + user + ".d_tonkhoct_"+mmyy+" set " + fie1 + "=" + fie1 + "+:soluong";
            sql += ", mabd=:mabd, handung=:handung, losx=:losx, madv=:madv, giamua=:giamua, giaban=:giaban where makho=:makho and idn=:idn and sttn=:sttn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                cmd.Parameters.Add("idn", NpgsqlDbType.Numeric).Value = idn;
                cmd.Parameters.Add("sttn", NpgsqlDbType.Numeric).Value = sttn;
                cmd.Parameters.Add("giaban", NpgsqlDbType.Numeric).Value = giaban;
                cmd.Parameters.Add("giamua", NpgsqlDbType.Numeric).Value = giamua;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    stt = get_capid;
                    sql = "insert into " + user + ".d_tonkhoct_"+ mmyy +"(stt,mabd,manguon,makho,handung,losx,madv,idn,sttn," + fie1 + ",giamua,giaban) values ";
                    sql += "(:stt,:mabd,:manguon,:makho,:handung,:losx,:madv,:idn,:sttn,:soluong,:giamua,:giaban)";
                    con = new NpgsqlConnection(sConn);
                    con.Open();
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("stt", NpgsqlDbType.Numeric).Value = stt;
                    cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                    cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                    cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                    cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                    cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                    cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                    cmd.Parameters.Add("idn", NpgsqlDbType.Numeric).Value = idn;
                    cmd.Parameters.Add("sttn", NpgsqlDbType.Numeric).Value = sttn;
                    cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                    cmd.Parameters.Add("giaban", NpgsqlDbType.Numeric).Value = giaban;
                    cmd.Parameters.Add("giamua", NpgsqlDbType.Numeric).Value = giamua;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_tonkhoct_"+mmyy);
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public long get_stt_tonkhoct(string mmyy, int makho, long idn, int sttn)
        {
            DataTable tmp = get_data("select stt from " + user + ".d_tonkhoct_"+mmyy+" where makho=" + makho + " and idn=" + idn + " and sttn=" + sttn).Tables[0];
            if (tmp.Rows.Count > 0) return long.Parse(tmp.Rows[0]["stt"].ToString());
            else return 0;
        }
        public bool upd_tonkhoct(string mmyy, int manguon, int makho, long stt, int mabd, string handung, string losx, int madv, decimal soluong, decimal giaban, decimal giamua, string fie1)
        {
            sql = "update " + user + ".d_tonkhoct_"+mmyy+" set " + fie1 + "=" + fie1 + "+:soluong,";
            sql+= "mabd=:mabd,manguon=:manguon,handung=:handung,losx=:losx,madv=:madv where makho=:makho and stt=:stt";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                cmd.Parameters.Add("stt", NpgsqlDbType.Numeric).Value = stt;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_tonkhoct_" + mmyy + "(makho,manguon,stt,idn,sttn,mabd,handung,losx,madv," + fie1 + ",giamua,giaban) values ";
                    sql += "(:makho,:manguon,:stt,0,0,:mabd,:handung,:losx,:madv,:soluong,:giamua,:giaban)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("stt", NpgsqlDbType.Numeric).Value = stt;
                    cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                    cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                    cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                    cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                    cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                    cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                    cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                    cmd.Parameters.Add("giamua", NpgsqlDbType.Numeric).Value = giamua;
                    cmd.Parameters.Add("giaban", NpgsqlDbType.Numeric).Value = giaban;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_tonkhoct_"+mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_tonkhoth(string mmyy, int makho, int manguon, int mabd, decimal soluong, string fie)
        {
            sql = "update " + user + ".d_tonkhoth_"+mmyy+" set " + fie + "=" + fie + "+:soluong where makho=:makho and manguon=:manguon and mabd=:mabd";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_tonkhoth_"+mmyy+"(makho,manguon,mabd," + fie + ") values ";
                    sql += "(:makho,:manguon,:mabd,:soluong)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("makho", NpgsqlDbType.Numeric).Value = makho;
                    cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                    cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                    cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_tonkhoth_"+mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public void upd_tonkhoth(string tt, string mmyy, int makho, int mabd, int manguon, decimal soluong, string fie)
        {
            if (tt == "delete") execute_data("update " + user + ".d_tonkhoth_"+mmyy+" set " + fie + "=" + fie + "-" + soluong + " where makho=" + makho + " and mabd=" + mabd + " and manguon=" + manguon);
            else if (tt == "insert") upd_tonkhoth(mmyy, makho, manguon, mabd, soluong, fie);
        }
        public DataTable get_toathuocct(string mmyy, DataTable dtct, long id)
        {
            bool bGiaban_dot = true;// bGiaban_theodot(get_nhomkho);
            DataTable dttmp = new DataTable();
            dttmp = dtct.Clone(); dttmp.Clear();
            long stt = 0;
            int irec = 0;
            DataRow[] dr;
            DataTable dttonct;
            decimal sl, sltx, sttx, slton, dongia, _giaban = 0;
            foreach (DataRow r in dtct.Select("soluong>0", "stt"))
            {
                if (r["sttt"].ToString() != "0")
                    updrec_toathuocct(dttmp, long.Parse(r["stt"].ToString()), long.Parse(r["sttt"].ToString()), int.Parse(r["mabd"].ToString()), r["ma"].ToString(), r["ten"].ToString(), r["tenhc"].ToString(), r["dang"].ToString(), r["tennguon"].ToString(), r["tenncc"].ToString(), r["handung"].ToString(), r["losx"].ToString(), decimal.Parse(r["soluong"].ToString()), decimal.Parse(r["giaban"].ToString()), decimal.Parse(r["sotienban"].ToString()), int.Parse(r["makho"].ToString()), int.Parse(r["manguon"].ToString()), null, decimal.Parse(r["soluongcu"].ToString()), decimal.Parse(r["sotiencu"].ToString()));
                else
                {
                    dttonct = get_tonkhoct(mmyy, int.Parse(r["makho"].ToString()), int.Parse(r["manguon"].ToString()), int.Parse(r["mabd"].ToString()));
                    sl = decimal.Parse(r["soluong"].ToString());
                    dr = dttonct.Select("true", "stt");
                    irec = dr.Length;
                    for (int i = 0; i < irec; i++)
                    {
                        slton = decimal.Parse(dr[i]["tondau"].ToString()) + decimal.Parse(dr[i]["slnhap"].ToString()) - decimal.Parse(dr[i]["slxuat"].ToString());
                        dongia = decimal.Parse(dr[i]["giamua"].ToString());
                        sltx = Math.Min(sl, slton);
                        if (sltx > 0)
                        {
                            stt = get_stt(dttmp);
                            sttx = dongia * sltx;
                            dr[i]["slxuat"] = decimal.Parse(dr[i]["slxuat"].ToString()) + sltx;
                            updrec_toathuocct(dttmp, stt, long.Parse(dr[i]["stt"].ToString()), int.Parse(r["mabd"].ToString()), r["ma"].ToString(), r["ten"].ToString(), r["tenhc"].ToString(), r["dang"].ToString(), dr[i]["tennguon"].ToString(), dr[i]["tenncc"].ToString(), dr[i]["handung"].ToString(), dr[i]["losx"].ToString(), sltx, (bGiaban_dot) ? decimal.Parse(dr[i]["giaban"].ToString()) : decimal.Parse(r["giaban"].ToString()), sltx * ((bGiaban_dot) ? decimal.Parse(dr[i]["giaban"].ToString()) : decimal.Parse(r["giaban"].ToString())), int.Parse(r["makho"].ToString()), int.Parse(dr[i]["manguon"].ToString()), null, decimal.Parse(r["soluongcu"].ToString()), decimal.Parse(r["sotiencu"].ToString()));
                            //
                            _giaban = (bGiaban_dot) ? decimal.Parse(dr[i]["giaban"].ToString()) : decimal.Parse(dr[i]["giaban_"].ToString());
                            upd_toathuocct(mmyy, id, stt, long.Parse(dr[i]["stt"].ToString()), int.Parse(dr[i]["manguon"].ToString()), int.Parse(dr[i]["makho"].ToString()), int.Parse(dr[i]["madv"].ToString()), int.Parse(dr[i]["mabd"].ToString()), dr[i]["handung"].ToString(), dr[i]["losx"].ToString(), sltx, _giaban, dongia);
                            upd_tonkhoct_xuat("insert", mmyy, long.Parse(dr[i]["stt"].ToString()), int.Parse(dr[i]["makho"].ToString()), int.Parse(dr[i]["manguon"].ToString()), int.Parse(dr[i]["mabd"].ToString()), dr[i]["handung"].ToString(), dr[i]["losx"].ToString(),int.Parse(dr[i]["madv"].ToString()), sltx, sttx, decimal.Parse(dr[i]["giaban"].ToString()), decimal.Parse(dr[i]["giamua"].ToString()));
                            //
                        }
                        sl -= sltx;
                        if (sl <= 0) break;
                    }
                    //if (sl > 0 && id != 0) upd_treo(id, long.Parse(r["stt"].ToString()), int.Parse(r["manguon"].ToString()), int.Parse(r["mabd"].ToString()), sl, "ngtruct", int.Parse(r["makho"].ToString()), 0);
                }
            }
            dttmp.AcceptChanges();
            return dttmp;
        }
        public int get_stt(DataTable dt)
        {
            int _stt = 0, var = 0;
            try
            {
                foreach (DataRow r in dt.Select("true", "stt"))
                {
                    _stt = int.Parse(r["stt"].ToString());
                    if ((var + 1) != _stt)
                    {
                        var = var + 1;
                        return var;
                    }
                    else var = _stt;
                }
                var += 1;
            }
            catch { var += 1; }
            return var;
        }
        public DataTable get_tonkhoct(string mmyy, int makho, int manguon, int mabd)
        {
            sql = "select a.*,b.ten as tennguon,c.ten as tenncc,d.giaban as giaban_ from " + user + ".d_tonkhoct_"+mmyy+" a," + user + ".d_dmnguon b," + user + ".d_dmnx c," + user + ".d_dmbd d where a.manguon=b.id and a.madv=c.id and a.mabd=d.id";
            sql += " and a.makho=" + makho + " and a.manguon=" + manguon + " and a.mabd=" + mabd + " and a.tondau+a.slnhap-a.slxuat>0";
            sql += " order by a.stt";
            DataTable tmp = get_data(sql).Tables[0];
            DataRow r1;
            string mmyys = "";
            foreach (DataRow r2 in get_data("select mmyy from " + user + ".table where substr(mmyy,3,2)||substr(mmyy,1,2)>'" + mmyy.Substring(2, 2) + mmyy.Substring(0, 2) + "'").Tables[0].Rows)
            {
                mmyys = r2["mmyy"].ToString();
                sql = "select a.stt,sum(a.slxuat) as soluong from " + user + ".d_tonkhoct_"+mmyys+" a," + user + ".d_dmkho d where a.makho=d.id and a.slxuat>0";
                sql += " and a.makho=" + makho + " and t.manguon=" + manguon + " and a.mabd=" + mabd;
                sql += " group by a.stt";
                foreach (DataRow r in get_data(sql).Tables[0].Rows)
                {
                    r1 = getrowbyid(tmp, "stt=" + long.Parse(r["stt"].ToString()));
                    if (r1 != null) r1["slxuat"] = decimal.Parse(r1["slxuat"].ToString()) + decimal.Parse(r["soluong"].ToString());
                }
            }
            return tmp;
        }
        public void updrec_toathuocct(DataTable dt, long stt, long sttt, int mabd, string ma, string ten, string tenhc, string dang, string tennguon, string tenncc, string handung, string losx, decimal soluong, decimal giaban, decimal sotienban, int makho, int manguon, DataTable dtton, decimal soluongcu, decimal sotiencu)
        {
            string exp = "stt=" + stt;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["stt"] = stt;
                nrow["sttt"] = sttt;
                nrow["mabd"] = mabd;
                nrow["ma"] = ma;
                nrow["ten"] = ten;
                nrow["tenhc"] = tenhc;
                nrow["dang"] = dang;
                nrow["handung"] = handung;
                nrow["losx"] = losx;
                nrow["manguon"] = manguon;
                nrow["tennguon"] = tennguon;
                nrow["tenncc"] = tenncc;
                nrow["makho"] = makho;
                nrow["soluong"] = soluong;
                nrow["sotienban"] = sotienban;
                nrow["giaban"] = giaban;
                nrow["soluongcu"] = soluongcu;
                nrow["sotiencu"] = sotiencu;
                dt.Rows.Add(nrow);
                if (dtton != null) updrec_tonkho_nguon(dtton, makho, mabd, manguon, soluong, "-");
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                if (dtton != null)
                {
                    decimal sl = decimal.Parse(dr[0]["soluong"].ToString());
                    updrec_tonkho_nguon(dtton, makho, mabd, manguon, sl, "+");
                    updrec_tonkho_nguon(dtton, makho, mabd, manguon, soluong, "-");
                }
                dr[0]["sttt"] = sttt;
                dr[0]["mabd"] = mabd;
                dr[0]["ma"] = ma;
                dr[0]["ten"] = ten;
                dr[0]["tenhc"] = tenhc;
                dr[0]["dang"] = dang;
                dr[0]["handung"] = handung;
                dr[0]["losx"] = losx;
                dr[0]["manguon"] = manguon;
                dr[0]["tennguon"] = tennguon;
                dr[0]["tenncc"] = tenncc;
                dr[0]["makho"] = makho;
                dr[0]["soluong"] = soluong;
                dr[0]["sotienban"] = sotienban;
                dr[0]["giaban"] = giaban;
                dr[0]["soluongcu"] = soluongcu;
                dr[0]["sotiencu"] = sotiencu;
            }
        }
        public void updrec_toathuocll(DataTable dt, long id, string mabn, string hoten, string namsinh, string ngay, string mabs, string makp,int userid, int sotoa, long maql)
        {
            string exp = "id=" + id;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["id"] = id;
                nrow["mabn"] = mabn;
                nrow["maql"] = maql;
                nrow["hoten"] = hoten;
                nrow["namsinh"] = namsinh;
                nrow["ngay"] = ngay;
                nrow["mabs"] = mabs;
                nrow["makp"] = makp;
                nrow["userid"] = userid;
                nrow["sotoa"] = sotoa;
                dt.Rows.Add(nrow);
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                dr[0]["mabn"] = mabn;
                dr[0]["maql"] = maql;
                dr[0]["hoten"] = hoten;
                dr[0]["namsinh"] = namsinh;
                dr[0]["ngay"] = ngay;
                dr[0]["mabs"] = mabs;
                dr[0]["makp"] = makp;
                dr[0]["userid"] = userid;
                dr[0]["sotoa"] = sotoa;
            }
            dt.AcceptChanges();
        }
        public void updrec_tonkho_nguon(DataTable dt, int kho, int mabd, int manguon, decimal soluong, string toantu)
        {
            DataRow[] r = dt.Select("makho=" + kho + " and id=" + mabd + " and manguon=" + manguon);
            if (r.Length > 0)
            {
                if (toantu == "+")
                    r[0]["soluong"] = decimal.Parse(r[0]["soluong"].ToString()) + soluong;
                else
                    r[0]["soluong"] = decimal.Parse(r[0]["soluong"].ToString()) - soluong;
            }
        }
        public int get_iXuat(string d_mmyy, int d_makho, long d_idn, int d_sttn)
        {
            ds = get_data("select mabd from " + user + ".d_tonkhoct_"+d_mmyy+" where makho=" + d_makho + " and idn=" + d_idn + " and sttn=" + d_sttn + " and slxuat>0");
            if (ds.Tables[0].Rows.Count == 0) return 0;
            else return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
        public bool upd_xuatll(string mmyy, long l_id, string sophieu, string ngay, string loai, int khox, int khon, string lydo, int userid, long l_idduyet)
        {
            sql = "update " + user + ".d_xuatll set mmyy=:mmyy,sophieu=:sophieu,ngay=to_timestamp(:ngay,'dd/mm/yyyy'),loai=:loai,khon=:khon,khox=:khox,lydo=:lydo,ngayud=now(),userid=:userid, idduyet=:l_idduyet where id=:l_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("mmyy", NpgsqlDbType.Varchar).Value = mmyy;
                cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                cmd.Parameters.Add("sophieu", NpgsqlDbType.Varchar).Value = sophieu;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("loai", NpgsqlDbType.Varchar).Value = loai;
                cmd.Parameters.Add("khox", NpgsqlDbType.Numeric).Value = khox;
                cmd.Parameters.Add("khon", NpgsqlDbType.Numeric).Value = khon;
                cmd.Parameters.Add("lydo", NpgsqlDbType.Text).Value = lydo;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("l_idduyet", NpgsqlDbType.Numeric).Value = l_idduyet;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_xuatll(id,sophieu,ngay,loai,khox,khon,lydo,userid,ngayud,mmyy,idduyet) values ";
                    sql += "(:l_id,:sophieu,to_timestamp(:ngay,'dd/mm/yyyy'),:loai,:khox,:khon,:lydo,:userid,now(),:mmyy,:l_idduyet)";
                    con = new NpgsqlConnection(sConn);
                    con.Open();
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("mmyy", NpgsqlDbType.Varchar).Value = mmyy;
                    cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                    cmd.Parameters.Add("sophieu", NpgsqlDbType.Varchar).Value = sophieu;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("loai", NpgsqlDbType.Varchar).Value = loai;
                    cmd.Parameters.Add("khox", NpgsqlDbType.Numeric).Value = khox;
                    cmd.Parameters.Add("khon", NpgsqlDbType.Numeric).Value = khon;
                    cmd.Parameters.Add("lydo", NpgsqlDbType.Text).Value = lydo;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("l_idduyet", NpgsqlDbType.Numeric).Value = l_idduyet;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_xuatll"+mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public void upd_xuatct(long l_id, int stt, long l_sttt, int mabd, int madv, int manguon, decimal soluong, string handung, string losx, decimal giamua,decimal giaban)
        {
            sql = "update " + user + ".d_xuatct set sttt=:l_sttt,mabd=:mabd,madv=:madv,manguon=:manguon,soluong=:soluong,handung=:handung,losx=:losx,giamua=:giamua,giaban=:giaban where id=:l_id and stt=:stt";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                cmd.Parameters.Add("stt", NpgsqlDbType.Numeric).Value = stt;
                cmd.Parameters.Add("l_sttt", NpgsqlDbType.Numeric).Value = l_sttt;
                cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                cmd.Parameters.Add("giamua", NpgsqlDbType.Numeric).Value = giamua;
                cmd.Parameters.Add("giaban", NpgsqlDbType.Numeric).Value = giaban;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".d_xuatct(id,stt,sttt,mabd,madv,manguon,soluong,handung,losx,giamua,giaban) values ";
                    sql += "(:l_id,:stt,:l_sttt,:mabd,:madv,:manguon,:soluong,:handung,:losx,:giamua,:giaban)";
                    con = new NpgsqlConnection(sConn);
                    con.Open();
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("l_id", NpgsqlDbType.Numeric).Value = l_id;
                    cmd.Parameters.Add("stt", NpgsqlDbType.Numeric).Value = stt;
                    cmd.Parameters.Add("l_sttt", NpgsqlDbType.Numeric).Value = l_sttt;
                    cmd.Parameters.Add("mabd", NpgsqlDbType.Numeric).Value = mabd;
                    cmd.Parameters.Add("madv", NpgsqlDbType.Numeric).Value = madv;
                    cmd.Parameters.Add("manguon", NpgsqlDbType.Numeric).Value = manguon;
                    cmd.Parameters.Add("soluong", NpgsqlDbType.Numeric).Value = soluong;
                    cmd.Parameters.Add("handung", NpgsqlDbType.Varchar).Value = handung;
                    cmd.Parameters.Add("losx", NpgsqlDbType.Varchar).Value = losx;
                    cmd.Parameters.Add("giamua", NpgsqlDbType.Numeric).Value = giamua;
                    cmd.Parameters.Add("giaban", NpgsqlDbType.Numeric).Value = giaban;
                    irec = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_xuatct");
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
        }
        public void updrec_xuatll(DataTable dt, long id, string sophieu, string ngay, int khox, int khon, string lydo)
        {
            string exp = "id=" + id;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["id"] = id;
                nrow["sophieu"] = sophieu;
                nrow["ngay"] = ngay;
                nrow["khox"] = khox;
                nrow["khon"] = khon;
                nrow["lydo"] = lydo;
                dt.Rows.Add(nrow);
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                dr[0]["id"] = id;
                dr[0]["ngay"] = ngay;
                dr[0]["khox"] = khox;
                dr[0]["khon"] = khon;
                dr[0]["lydo"] = lydo;
            }
            dt.AcceptChanges();
        }
        public void updrec_xuatct(DataTable dt, int i_stt, long l_sttt, int i_mabd, int i_madv, int i_manguon, int i_makho, string m_ma, string m_ten, string m_tenhc, string m_dang, string m_handung, string m_losx, decimal d_soluong, decimal d_giamua, decimal d_sotien, decimal d_giaban, DataTable dtton)
        {
            string exp = "mabd=" + i_mabd + " and stt=" + i_stt;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["stt"] = i_stt;
                nrow["sttt"] = l_sttt;
                nrow["mabd"] = i_mabd;
                nrow["madv"] = i_madv;
                nrow["manguon"] = i_manguon;
                nrow["ma"] = m_ma;
                nrow["ten"] = m_ten;
                nrow["tenhc"] = m_tenhc;
                nrow["dang"] = m_dang;
                nrow["handung"] = m_handung;
                nrow["losx"] = m_losx;
                nrow["soluong"] = d_soluong;
                nrow["giamua"] = d_giamua;
                nrow["sotien"] = d_sotien;
                nrow["giaban"] = d_giaban;
                dt.Rows.Add(nrow);
                if (dtton != null) updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, d_soluong, "-");
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                if (dtton != null)
                {
                    decimal sl = decimal.Parse(dr[0]["soluong"].ToString());
                    updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, sl, "+");
                    updrec_tonkho_nguon(dtton, i_makho, i_mabd, i_manguon, d_soluong, "-");
                }
                dr[0]["stt"] = i_stt;
                dr[0]["sttt"] = l_sttt;
                dr[0]["mabd"] = i_mabd;
                dr[0]["madv"] = i_madv;
                dr[0]["manguon"] = i_manguon;
                dr[0]["ma"] = m_ma;
                dr[0]["ten"] = m_ten;
                dr[0]["tenhc"] = m_tenhc;
                dr[0]["dang"] = m_dang;
                dr[0]["handung"] = m_handung;
                dr[0]["losx"] = m_losx;
                dr[0]["soluong"] = d_soluong;
                dr[0]["giamua"] = d_giamua;
                dr[0]["sotien"] = d_sotien;
                dr[0]["giaban"] = d_giaban;
            }
            dt.AcceptChanges();
        }
        public void updrec_nhapct(DataTable dt, long stt, int mabd, string ma, string ten, string tenhc, string dang, string handung, string losx, int madv, decimal soluong, decimal dongia, decimal sotien, int vat, decimal sotienvat, decimal giaban, decimal giamua, decimal sl1, decimal sl2, decimal tyle, decimal giabancu, decimal giamuacu, decimal tongtien)
        {
            string exp = "mabd=" + mabd;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["stt"] = stt;
                nrow["mabd"] = mabd;
                nrow["ma"] = ma;
                nrow["ten"] = ten;
                nrow["tenhc"] = tenhc;
                nrow["dang"] = dang;
                nrow["handung"] = handung;
                nrow["losx"] = losx;
                nrow["madv"] = madv;
                nrow["soluong"] = soluong;
                nrow["dongia"] = dongia;
                nrow["sotien"] = sotien;
                nrow["vat"] = vat;
                nrow["sotienvat"] = sotienvat;
                nrow["giaban"] = giaban;
                nrow["giamua"] = giamua;
                nrow["sl1"] = sl1;
                nrow["sl2"] = sl2;
                nrow["tyle"] = tyle;
                nrow["giabancu"] = giabancu;
                nrow["giamuacu"] = giamuacu;
                nrow["tongtien"] = tongtien;
                dt.Rows.Add(nrow);
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                dr[0]["mabd"] = mabd;
                dr[0]["ma"] = ma;
                dr[0]["ten"] = ten;
                dr[0]["tenhc"] = tenhc;
                dr[0]["dang"] = dang;
                dr[0]["handung"] = handung;
                dr[0]["losx"] = losx;
                dr[0]["madv"] = madv;
                dr[0]["soluong"] = soluong;
                dr[0]["dongia"] = dongia;
                dr[0]["sotien"] = sotien;
                dr[0]["vat"] = vat;
                dr[0]["sotienvat"] = sotienvat;
                dr[0]["giaban"] = giaban;
                dr[0]["giamua"] = giamua;
                dr[0]["sl1"] = sl1;
                dr[0]["sl2"] = sl2;
                dr[0]["tyle"] = tyle;
                dr[0]["giabancu"] = giabancu;
                dr[0]["giamuacu"] = giamuacu;
                dr[0]["tongtien"] = tongtien;
            }
        }
        public void updrec_nhapll(DataTable dt, long id, string sophieu, string ngaysp, string sohd, string ngayhd, string nguoigiao, int madv, int makho, int manguon, string no, string co, decimal chietkhau)
        {
            string exp = "id=" + id;
            DataRow r = getrowbyid(dt, exp);
            if (r == null)
            {
                DataRow nrow = dt.NewRow();
                nrow["id"] = id;
                nrow["sophieu"] = sophieu;
                nrow["ngaysp"] = ngaysp;
                nrow["sohd"] = sohd;
                nrow["ngayhd"] = ngayhd;
                nrow["nguoigiao"] = nguoigiao;
                nrow["madv"] = madv;
                nrow["makho"] = makho;
                nrow["manguon"] = manguon;
                nrow["no"] = no;
                nrow["co"] = co;
                nrow["chietkhau"] = chietkhau;
                dt.Rows.Add(nrow);
            }
            else
            {
                DataRow[] dr = dt.Select(exp);
                dr[0]["sophieu"] = sophieu;
                dr[0]["ngaysp"] = ngaysp;
                dr[0]["sohd"] = sohd;
                dr[0]["ngayhd"] = ngayhd;
                dr[0]["nguoigiao"] = nguoigiao;
                dr[0]["madv"] = madv;
                dr[0]["makho"] = makho;
                dr[0]["manguon"] = manguon;
                dr[0]["no"] = no;
                dr[0]["co"] = co;
                dr[0]["chietkhau"] = chietkhau;
            }
        }
        public bool upd_nhapll(string d_mmyy, long d_id, string d_sophieu, string d_ngaysp, string d_sohd, string d_ngayhd, string d_loai, string d_nguoigiao, int d_madv, int d_makho, int d_manguon, string d_no, string d_co, int d_userid, decimal d_chietkhau)
        {
            sql = user + ".upd_nhapll_"+d_mmyy;
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("d_id", NpgsqlDbType.Numeric).Value = d_id;
                cmd.Parameters.Add("d_sophieu", NpgsqlDbType.Varchar, 10).Value = d_sophieu;
                cmd.Parameters.Add("d_ngaysp", NpgsqlDbType.Varchar, 10).Value = d_ngaysp;
                cmd.Parameters.Add("d_sohd", NpgsqlDbType.Text).Value = d_sohd;
                cmd.Parameters.Add("d_ngayhd", NpgsqlDbType.Varchar, 10).Value = d_ngayhd;
                cmd.Parameters.Add("d_loai", NpgsqlDbType.Varchar, 1).Value = d_loai;
                cmd.Parameters.Add("d_nguoigiao", NpgsqlDbType.Text).Value = d_nguoigiao;
                cmd.Parameters.Add("d_madv", NpgsqlDbType.Numeric).Value = d_madv;
                cmd.Parameters.Add("d_makho", NpgsqlDbType.Numeric).Value = d_makho;
                cmd.Parameters.Add("d_manguon", NpgsqlDbType.Numeric).Value = d_manguon;
                cmd.Parameters.Add("d_no", NpgsqlDbType.Text).Value = d_no;
                cmd.Parameters.Add("d_co", NpgsqlDbType.Varchar, 10).Value = d_co;
                cmd.Parameters.Add("d_userid", NpgsqlDbType.Numeric).Value = d_userid;
                cmd.Parameters.Add("d_chietkhau", NpgsqlDbType.Numeric).Value = d_chietkhau;
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_nhapll_"+d_mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_nhapct(string d_mmyy, long d_id, long d_stt, int d_mabd,int d_madv, string d_handung, string d_losx, int d_vat, decimal d_soluong, decimal d_dongia, decimal d_sotien, decimal d_giaban, decimal d_giamua, decimal d_sl1, decimal d_sl2, decimal d_tyle, decimal d_giabancu, decimal d_giamuacu, decimal d_tyle_ggia, decimal d_st_ggia)
        {
            sql = user + ".upd_nhapct_" + d_mmyy;
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("d_id", NpgsqlDbType.Numeric).Value = d_id;
                cmd.Parameters.Add("d_stt", NpgsqlDbType.Numeric).Value = d_stt;
                cmd.Parameters.Add("d_mabd", NpgsqlDbType.Numeric).Value = d_mabd;
                cmd.Parameters.Add("d_madv", NpgsqlDbType.Numeric).Value = d_madv;
                cmd.Parameters.Add("d_handung", NpgsqlDbType.Varchar, 4).Value = d_handung;
                cmd.Parameters.Add("d_losx", NpgsqlDbType.Varchar, 20).Value = d_losx;
                cmd.Parameters.Add("d_vat", NpgsqlDbType.Numeric).Value = d_vat;
                cmd.Parameters.Add("d_soluong", NpgsqlDbType.Numeric).Value = d_soluong;
                cmd.Parameters.Add("d_dongia", NpgsqlDbType.Numeric).Value = d_dongia;
                cmd.Parameters.Add("d_sotien", NpgsqlDbType.Numeric).Value = d_sotien;
                cmd.Parameters.Add("d_giaban", NpgsqlDbType.Numeric).Value = d_giaban;
                cmd.Parameters.Add("d_giamua", NpgsqlDbType.Numeric).Value = d_giamua;
                cmd.Parameters.Add("d_sl1", NpgsqlDbType.Numeric).Value = d_sl1;
                cmd.Parameters.Add("d_sl2", NpgsqlDbType.Numeric).Value = d_sl2;
                cmd.Parameters.Add("d_tyle", NpgsqlDbType.Numeric).Value = d_tyle;
                cmd.Parameters.Add("d_giabancu", NpgsqlDbType.Numeric).Value = d_giabancu;
                cmd.Parameters.Add("d_giamuacu", NpgsqlDbType.Numeric).Value = d_giamuacu;
                cmd.Parameters.Add("d_tyle_ggia", NpgsqlDbType.Numeric).Value = d_tyle_ggia;
                cmd.Parameters.Add("d_st_ggia", NpgsqlDbType.Numeric).Value = d_st_ggia;
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "d_nhapct_"+d_mmyy);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        #region Khambenh
        public bool upd_khamvu(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string vutrai, string vuphai, string xetnghiem,string ketluan, string mabs)
        {
            sql = "update " + user + ".ba_khamvu set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, vutrai=:vutrai, vuphai=:vuphai, xetnghiem=:xetnghiem, ketluan=:ketluan, mabs=:mabs, ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("vutrai", NpgsqlDbType.Text).Value = vutrai;
                cmd.Parameters.Add("vuphai", NpgsqlDbType.Text).Value = vuphai;
                cmd.Parameters.Add("xetnghiem", NpgsqlDbType.Text).Value = xetnghiem;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_khamvu (mabn,maql,makp,ngay,lanthu,vutrai,vuphai,xetnghiem,ketluan,mabs,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:vutrai,:vuphai,:xetnghiem,:ketluan,:mabs,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("vutrai", NpgsqlDbType.Text).Value = vutrai;
                    cmd.Parameters.Add("vuphai", NpgsqlDbType.Text).Value = vuphai;
                    cmd.Parameters.Add("xetnghiem", NpgsqlDbType.Text).Value = xetnghiem;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_khamvu");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_canlamsang(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string ten, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_canlamsang set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, ten=:ten, ketluan=:ketluan, mabs=:mabs, userid=:userid,ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("ten", NpgsqlDbType.Text).Value = ten;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_canlamsang (mabn,maql,makp,ngay,lanthu,ten,ketluan,mabs,userid,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:ten,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("ten", NpgsqlDbType.Text).Value = ten;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_canlamsang");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_theodoitimthai(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string tanso,string nhiptim, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_theodoitimthai set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, tanso=:tanso, nhiptim=:nhiptim, ketluan=:ketluan, mabs=:mabs, userid=:userid,ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("tanso", NpgsqlDbType.Text).Value = tanso;
                cmd.Parameters.Add("nhiptim", NpgsqlDbType.Text).Value = nhiptim;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_theodoitimthai (mabn,maql,makp,ngay,lanthu,tanso,nhiptim,ketluan,mabs,userid,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:tanso,:nhiptim,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("tanso", NpgsqlDbType.Text).Value = tanso;
                    cmd.Parameters.Add("nhiptim", NpgsqlDbType.Text).Value = nhiptim;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_theodoitimthai");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuammau(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string dopler, string dmron,string dmtucung_p,string dmtucung_t,string dmnao,string chisonao, string ketluan, string mabs, int userid,string slthai,string timthai,string cudongthai,string duongkinh,string xuongdui,string rau,string oi)
        {
            sql = "update " + user + ".ba_sieuammau set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, dopler=:dopler, dmron=:dmron,dmtucung_p=:dmtucung_p, dmtucung_t=:dmtucung_t,dmnao=:dmnao,chisonao=:chisonao, ketluan=:ketluan, mabs=:mabs, userid=:userid,ngayud=now(),slthai=:slthai,timthai=:timthai,cudongthai=:cudongthai,duongkinh=:cudongthai,xuongdui=:xuongdui,rau=:rau,oi=:oi where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("dopler", NpgsqlDbType.Text).Value = dopler;
                cmd.Parameters.Add("dmron", NpgsqlDbType.Text).Value = dmron;
                cmd.Parameters.Add("dmtucung_p", NpgsqlDbType.Text).Value = dmtucung_p;
                cmd.Parameters.Add("dmtucung_t", NpgsqlDbType.Text).Value = dmtucung_t;
                cmd.Parameters.Add("dmnao", NpgsqlDbType.Text).Value = dmnao;
                cmd.Parameters.Add("chisonao", NpgsqlDbType.Text).Value = chisonao;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                //
                cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                cmd.Parameters.Add("rau", NpgsqlDbType.Text).Value = rau;
                cmd.Parameters.Add("oi", NpgsqlDbType.Text).Value = oi;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuammau (mabn,maql,makp,ngay,lanthu,dopler,dmron,dmtucung_p,dmtucung_t,dmnao,chisonao,ketluan,mabs,userid,ngayud,slthai,timthai,cudongthai,duongkinh,xuongdui,rau,oi) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:dopler,:dmron,:dmtucung_p,:dmtucung_t,:dmnao,:chisonao,:ketluan,:mabs,:userid,now(),:slthai,:timthai,:cudongthai,:duongkinh,:xuongdui,:rau,:oi)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("dopler", NpgsqlDbType.Text).Value = dopler;
                    cmd.Parameters.Add("dmron", NpgsqlDbType.Text).Value = dmron;
                    cmd.Parameters.Add("dmtucung_p", NpgsqlDbType.Text).Value = dmtucung_p;
                    cmd.Parameters.Add("dmtucung_t", NpgsqlDbType.Text).Value = dmtucung_t;
                    cmd.Parameters.Add("dmnao", NpgsqlDbType.Text).Value = dmnao;
                    cmd.Parameters.Add("chisonao", NpgsqlDbType.Text).Value = chisonao;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                    cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                    cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                    cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                    cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                    cmd.Parameters.Add("rau", NpgsqlDbType.Text).Value = rau;
                    cmd.Parameters.Add("oi", NpgsqlDbType.Text).Value = oi;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuammau");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_hiemmuon(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string toantrang, string vu, string tucung, string cotucung, string phanphu, string xetnghiem, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_hiemmuon set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, toantrang=:toantrang, vu=:vu, tucung=:tucung, cotucung=:cotucung, phanphu=:phanphu, xetnghiem=:xetnghiem, ketluan=:ketluan, mabs=:mabs, userid=:userid, ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("toantrang", NpgsqlDbType.Text).Value = toantrang;
                cmd.Parameters.Add("vu", NpgsqlDbType.Text).Value = vu;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("cotucung", NpgsqlDbType.Text).Value = cotucung;
                cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                cmd.Parameters.Add("xetnghiem", NpgsqlDbType.Text).Value = xetnghiem;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_hiemmuon (mabn,maql,makp,ngay,lanthu,toantrang,vu,tucung,cotucung,phanphu,xetnghiem,ketluan,mabs,userid,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:toantrang,:vu,:tucung,:cotucung,:phanphu,:xetnghiem,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("toantrang", NpgsqlDbType.Text).Value = toantrang;
                    cmd.Parameters.Add("vu", NpgsqlDbType.Text).Value = vu;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("cotucung", NpgsqlDbType.Text).Value = cotucung;
                    cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                    cmd.Parameters.Add("xetnghiem", NpgsqlDbType.Text).Value = xetnghiem;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_hiemmuon");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuampk(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string yeucau, string tucung, string phanphu_p, string phanphu_t, string tieukhung, string khac, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_sieuampk set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, yeucau=:yeucau, tucung=:tucung, phanphu_p=:phanphu_p, phanphu_t=:phanphu_t, tieukhung=:tieukhung, khac=:khac, ketluan=:ketluan, mabs=:mabs, userid=:userid, ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("yeucau", NpgsqlDbType.Text).Value = yeucau;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("phanphu_p", NpgsqlDbType.Text).Value = phanphu_p;
                cmd.Parameters.Add("phanphu_t", NpgsqlDbType.Text).Value = phanphu_t;
                cmd.Parameters.Add("tieukhung", NpgsqlDbType.Text).Value = tieukhung;
                cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuampk (mabn,maql,makp,ngay,lanthu,yeucau,tucung,phanphu_p,phanphu_t,tieukhung,khac,ketluan,mabs,userid,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:yeucau,:tucung,:phanphu_p,:phanphu_t,:tieukhung,:khac,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("yeucau", NpgsqlDbType.Text).Value = yeucau;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("phanphu_p", NpgsqlDbType.Text).Value = phanphu_p;
                    cmd.Parameters.Add("phanphu_t", NpgsqlDbType.Text).Value = phanphu_t;
                    cmd.Parameters.Add("tieukhung", NpgsqlDbType.Text).Value = tieukhung;
                    cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuampk");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuamddad(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string yeucau, string tucung, string phanphu_p, string phanphu_t, string tieukhung, string khac, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_sieuamddad set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, yeucau=:yeucau, tucung=:tucung, phanphu_p=:phanphu_p, phanphu_t=:phanphu_t, tieukhung=:tieukhung, khac=:khac, ketluan=:ketluan, mabs=:mabs, userid=:userid, ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("yeucau", NpgsqlDbType.Text).Value = yeucau;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("phanphu_p", NpgsqlDbType.Text).Value = phanphu_p;
                cmd.Parameters.Add("phanphu_t", NpgsqlDbType.Text).Value = phanphu_t;
                cmd.Parameters.Add("tieukhung", NpgsqlDbType.Text).Value = tieukhung;
                cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuamddad (mabn,maql,makp,ngay,lanthu,yeucau,tucung,phanphu_p,phanphu_t,tieukhung,khac,ketluan,mabs,userid,ngayud) values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:yeucau,:tucung,:phanphu_p,:phanphu_t,:tieukhung,:khac,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("yeucau", NpgsqlDbType.Text).Value = yeucau;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("phanphu_p", NpgsqlDbType.Text).Value = phanphu_p;
                    cmd.Parameters.Add("phanphu_t", NpgsqlDbType.Text).Value = phanphu_t;
                    cmd.Parameters.Add("tieukhung", NpgsqlDbType.Text).Value = tieukhung;
                    cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuamddad");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_khampk(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string para, string tiensu, int tuoicokinh, int tinhchat, int luongkinh, string phanphu, string cotucung, string obung, string khac, string soituoi, string soictc, string ketluan, string mabs, int userid, string tucung, string kinhcuoi, string kinhnguyet,string datvong)
        {
            sql = "update " + user + ".ba_khampk set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, para=:para, tiensu=:tiensu, tuoicokinh=:tuoicokinh, tinhchat=:tinhchat, luongkinh=:luongkinh, phanphu=:phanphu, cotucung=:cotucung, obung=:obung, khac=:khac, soituoi=:soituoi, soictc=:soictc, ketluan=:ketluan,mabs=:mabs,userid=:userid,ngayud=now(),tucung=:tucung,kinhcuoi=:kinhcuoi,kinhnguyet=:kinhnguyet,datvong=:datvong where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("para", NpgsqlDbType.Text).Value = para;
                cmd.Parameters.Add("tiensu", NpgsqlDbType.Text).Value = tiensu;
                cmd.Parameters.Add("tuoicokinh", NpgsqlDbType.Numeric).Value = tuoicokinh;
                cmd.Parameters.Add("tinhchat", NpgsqlDbType.Numeric).Value = tinhchat;
                cmd.Parameters.Add("luongkinh", NpgsqlDbType.Numeric).Value = luongkinh;
                cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                cmd.Parameters.Add("cotucung", NpgsqlDbType.Text).Value = cotucung;
                cmd.Parameters.Add("obung", NpgsqlDbType.Text).Value = obung;
                cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                cmd.Parameters.Add("soituoi", NpgsqlDbType.Text).Value = soituoi;
                cmd.Parameters.Add("soictc", NpgsqlDbType.Text).Value = soictc;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("kinhcuoi", NpgsqlDbType.Text).Value = kinhcuoi;
                cmd.Parameters.Add("kinhnguyet", NpgsqlDbType.Text).Value = kinhnguyet;
                cmd.Parameters.Add("datvong", NpgsqlDbType.Text).Value = datvong;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_khampk (mabn,maql,makp,ngay,lanthu,para,tiensu,tuoicokinh,tinhchat,luongkinh,phanphu,cotucung,obung,khac,soituoi,soictc,ketluan,mabs,userid,ngayud,tucung,kinhcuoi,kinhnguyet,datvong) ";
                    sql+=" values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:para,:tiensu,:tuoicokinh,:tinhchat,:luongkinh,:phanphu,:cotucung,:obung,:khac,:soituoi,:soictc,:ketluan,:mabs,:userid,now(),:tucung,:kinhcuoi,:kinhnguyet,:datvong)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("para", NpgsqlDbType.Text).Value = para;
                    cmd.Parameters.Add("tiensu", NpgsqlDbType.Text).Value = tiensu;
                    cmd.Parameters.Add("tuoicokinh", NpgsqlDbType.Numeric).Value = tuoicokinh;
                    cmd.Parameters.Add("tinhchat", NpgsqlDbType.Numeric).Value = tinhchat;
                    cmd.Parameters.Add("luongkinh", NpgsqlDbType.Numeric).Value = luongkinh;
                    cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                    cmd.Parameters.Add("cotucung", NpgsqlDbType.Text).Value = cotucung;
                    cmd.Parameters.Add("obung", NpgsqlDbType.Text).Value = obung;
                    cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                    cmd.Parameters.Add("soituoi", NpgsqlDbType.Text).Value = soituoi;
                    cmd.Parameters.Add("soictc", NpgsqlDbType.Text).Value = soictc;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("kinhcuoi", NpgsqlDbType.Text).Value = kinhcuoi;
                    cmd.Parameters.Add("kinhnguyet", NpgsqlDbType.Text).Value = kinhnguyet;
                    cmd.Parameters.Add("datvong", NpgsqlDbType.Text).Value = datvong;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_khampk");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuamthai(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string tucung, string phanphu, string buongtucung, string soluongthai, string sodothai, string rauthai, string oithai, string khac, string ketluan, string mabs, int userid,string timthai,string cudongthai,string dudoansinh)
        {
            sql = "update " + user + ".ba_sieuamthai set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, tucung=:tucung, phanphu=:phanphu, buongtucung=:buongtucung, soluongthai=:soluongthai, sodothai=:sodothai, rauthai=:rauthai, oithai=:oithai, khac=:khac, ketluan=:ketluan, mabs=:mabs,userid=:userid,ngayud=now(),timthai=:timthai,cudongthai=:cudongthai, dudoansinh=:dudoansinh where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                cmd.Parameters.Add("buongtucung", NpgsqlDbType.Text).Value = buongtucung;
                cmd.Parameters.Add("soluongthai", NpgsqlDbType.Text).Value = soluongthai;
                cmd.Parameters.Add("sodothai", NpgsqlDbType.Text).Value = sodothai;
                cmd.Parameters.Add("rauthai", NpgsqlDbType.Text).Value = rauthai;
                cmd.Parameters.Add("oithai", NpgsqlDbType.Text).Value = oithai;
                cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuamthai (mabn,maql,makp,ngay,lanthu,tucung,phanphu,buongtucung,soluongthai,sodothai,rauthai,oithai,khac,ketluan,mabs,userid,ngayud,timthai,cudongthai,dudoansinh) ";
                    sql += " values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:tucung,:phanphu,:buongtucung,:soluongthai,:sodothai,:rauthai,:oithai,:khac,:ketluan,:mabs,:userid,now(),:timthai,:cudongthai,:dudoansinh)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                    cmd.Parameters.Add("buongtucung", NpgsqlDbType.Text).Value = buongtucung;
                    cmd.Parameters.Add("soluongthai", NpgsqlDbType.Text).Value = soluongthai;
                    cmd.Parameters.Add("sodothai", NpgsqlDbType.Text).Value = sodothai;
                    cmd.Parameters.Add("rauthai", NpgsqlDbType.Text).Value = rauthai;
                    cmd.Parameters.Add("oithai", NpgsqlDbType.Text).Value = oithai;
                    cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                    cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                    cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuamthai");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuamthai_3thang(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string tucung, string slthai, string timthai, string cudongthai, string duongkinh, string xuongdui, string trungbinhbung, string chuvibung, string rauthai, string oithai, string trongluong,string dudoansinh, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_sieuamthai_3thang set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, tucung=:tucung, soluongthai=:slthai, timthai=:timthai, cudongthai=:cudongthai, duongkinh=:duongkinh, xuongdui=:xuongdui, trungbinhbung=:trungbinhbung, chuvibung=:chuvibung,rauthai=:rauthai,oithai=:oithai,trongluongthai=:trongluong,dudoansinh=:dudoansinh, ketluan=:ketluan, mabs=:mabs,userid=:userid,ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                cmd.Parameters.Add("trungbinhbung", NpgsqlDbType.Text).Value = trungbinhbung;
                cmd.Parameters.Add("chuvibung", NpgsqlDbType.Text).Value = chuvibung;
                cmd.Parameters.Add("rauthai", NpgsqlDbType.Text).Value = rauthai;
                cmd.Parameters.Add("oithai", NpgsqlDbType.Text).Value = oithai;
                cmd.Parameters.Add("trongluong", NpgsqlDbType.Text).Value = trongluong;
                cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuamthai_3thang (mabn,maql,makp,ngay,lanthu,tucung,soluongthai,timthai,cudongthai,duongkinh,xuongdui,trungbinhbung,chuvibung,rauthai,oithai,trongluongthai,dudoansinh,ketluan,mabs,userid,ngayud) ";
                    sql += " values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:tucung,:slthai,:timthai,:cudongthai,:duongkinh,:xuongdui,:trungbinhbung,:chuvibung,:rauthai,:oithai,:trongluong,:dudoansinh,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                    cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                    cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                    cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                    cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                    cmd.Parameters.Add("trungbinhbung", NpgsqlDbType.Text).Value = trungbinhbung;
                    cmd.Parameters.Add("chuvibung", NpgsqlDbType.Text).Value = chuvibung;
                    cmd.Parameters.Add("rauthai", NpgsqlDbType.Text).Value = rauthai;
                    cmd.Parameters.Add("oithai", NpgsqlDbType.Text).Value = oithai;
                    cmd.Parameters.Add("trongluong", NpgsqlDbType.Text).Value = trongluong;
                    cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuamthai_3thang");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_sieuam4d_12tuan(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string tucung,string phanphu, string slthai, string timthai, string cudongthai, string duongkinh, string xuongdui, string vomso, string cotsong, string chan, string tay, string longnguc,string phoi,string khoangsang,string obung,string vitrirau,string nuocoi,string tuoithai_kcc,string tuoithai_sa, string trongluongthai, string dudoansinh, string nhanxet,string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_sieuam4d_12tuan set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, tucung=:tucung, phanphu=:phanphu,soluongthai=:slthai, timthai=:timthai, cudongthai=:cudongthai, duongkinh=:duongkinh, xuongdui=:xuongdui, vomso=:vomso, cotsong=:cotsong,chan=:chan,tay=:tay,longnguc=:longnguc,phoi=:phoi,khoangsang=:khoangsang,obung=:obung,vitrirau=:vitrirau,nuocoi=:nuocoi,tuoithai_kcc=:tuoithai_kcc,tuoithai_sa=:tuoithai_sa,trongluongthai=:trongluongthai,dudoansinh=:dudoansinh, nhanxet=:nhanxet, ketluan=:ketluan, mabs=:mabs,userid=:userid,ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                cmd.Parameters.Add("vomso", NpgsqlDbType.Text).Value = vomso;
                cmd.Parameters.Add("cotsong", NpgsqlDbType.Text).Value = cotsong;
                cmd.Parameters.Add("chan", NpgsqlDbType.Text).Value = chan;
                cmd.Parameters.Add("tay", NpgsqlDbType.Text).Value = tay;
                cmd.Parameters.Add("longnguc", NpgsqlDbType.Text).Value = longnguc;
                cmd.Parameters.Add("phoi", NpgsqlDbType.Text).Value = phoi;
                cmd.Parameters.Add("khoangsang", NpgsqlDbType.Text).Value = khoangsang;
                cmd.Parameters.Add("obung", NpgsqlDbType.Text).Value = obung;
                cmd.Parameters.Add("vitrirau", NpgsqlDbType.Text).Value = vitrirau;
                cmd.Parameters.Add("nuocoi", NpgsqlDbType.Text).Value = nuocoi;
                cmd.Parameters.Add("tuoithai_kcc", NpgsqlDbType.Text).Value = tuoithai_kcc;
                cmd.Parameters.Add("tuoithai_sa", NpgsqlDbType.Text).Value = tuoithai_sa;
                cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Text).Value = trongluongthai;
                cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                cmd.Parameters.Add("nhanxet", NpgsqlDbType.Text).Value = nhanxet;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuam4d_12tuan (mabn,maql,makp,ngay,lanthu,tucung,phanphu,soluongthai,timthai,cudongthai,duongkinh,xuongdui,vomso,cotsong,chan,tay,longnguc,phoi,khoangsang,obung,vitrirau,nuocoi,tuoithai_kcc,tuoithai_sa,trongluongthai,dudoansinh,nhanxet,ketluan,mabs,userid,ngayud) ";
                    sql += " values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:tucung,:phanphu,:slthai,:timthai,:cudongthai,:duongkinh,:xuongdui,:vomso,:cotsong,:chan,:tay,:longnguc,:phoi,:khoangsang,:obung,:vitrirau,:nuocoi,:tuoithai_kcc,:tuoithai_sa,:trongluongthai,:dudoansinh,:nhanxet,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                    cmd.Parameters.Add("slthai", NpgsqlDbType.Text).Value = slthai;
                    cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                    cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                    cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                    cmd.Parameters.Add("xuongdui", NpgsqlDbType.Text).Value = xuongdui;
                    cmd.Parameters.Add("vomso", NpgsqlDbType.Text).Value = vomso;
                    cmd.Parameters.Add("cotsong", NpgsqlDbType.Text).Value = cotsong;
                    cmd.Parameters.Add("chan", NpgsqlDbType.Text).Value = chan;
                    cmd.Parameters.Add("tay", NpgsqlDbType.Text).Value = tay;
                    cmd.Parameters.Add("longnguc", NpgsqlDbType.Text).Value = longnguc;
                    cmd.Parameters.Add("phoi", NpgsqlDbType.Text).Value = phoi;
                    cmd.Parameters.Add("khoangsang", NpgsqlDbType.Text).Value = khoangsang;
                    cmd.Parameters.Add("obung", NpgsqlDbType.Text).Value = obung;
                    cmd.Parameters.Add("vitrirau", NpgsqlDbType.Text).Value = vitrirau;
                    cmd.Parameters.Add("nuocoi", NpgsqlDbType.Text).Value = nuocoi;
                    cmd.Parameters.Add("tuoithai_kcc", NpgsqlDbType.Text).Value = tuoithai_kcc;
                    cmd.Parameters.Add("tuoithai_sa", NpgsqlDbType.Text).Value = tuoithai_sa;
                    cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Text).Value = trongluongthai;
                    cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                    cmd.Parameters.Add("nhanxet", NpgsqlDbType.Text).Value = nhanxet;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuam4d_12tuan");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_khamthai(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string para, string ngaykinhc, string tiensu, 
            string huyetap, string timphoi, string phune, string khac, string soluongthai, int ngoithai, string sodothai, string timthai, string trongluongthai, 
            string dukiensinh, string xnmau, string xnntieu, string sodokhac, string ketluan, string mabs, int userid)
        {
            sql = "update " + user + ".ba_khamthai set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, para=:para, ngaykinhc=:ngaykinhc,tiensu=:tiensu,";
            sql+=" huyetap=:huyetap, timphoi=:timphoi, phune=:phune, khac=:khac, soluongthai=:soluongthai,ngoithai=:ngoithai, sodothai=:sodothai, timthai=:timthai, trongluongthai=:trongluongthai, dukiensinh=to_timestamp(:dukiensinh,'dd/mm/yyyy'), xnmau=:xnmau, xnnuoctieu=:xnntieu,sodokhac=:sodokhac, ketluan=:ketluan,mabs=:mabs,userid=:userid,ngayud=now() where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("para", NpgsqlDbType.Text).Value = para;
                cmd.Parameters.Add("ngaykinhc", NpgsqlDbType.Varchar).Value = ngaykinhc;
                cmd.Parameters.Add("tiensu", NpgsqlDbType.Text).Value = tiensu;
                cmd.Parameters.Add("huyetap", NpgsqlDbType.Text).Value = huyetap;
                cmd.Parameters.Add("timphoi", NpgsqlDbType.Text).Value = timphoi;
                cmd.Parameters.Add("phune", NpgsqlDbType.Text).Value = phune;
                cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                cmd.Parameters.Add("soluongthai", NpgsqlDbType.Text).Value = soluongthai;
                cmd.Parameters.Add("ngoithai", NpgsqlDbType.Numeric).Value = ngoithai;
                cmd.Parameters.Add("sodothai", NpgsqlDbType.Text).Value = sodothai;
                cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Varchar).Value = trongluongthai;
                cmd.Parameters.Add("dukiensinh", NpgsqlDbType.Varchar).Value = dukiensinh;
                cmd.Parameters.Add("xnmau", NpgsqlDbType.Text).Value = xnmau;
                cmd.Parameters.Add("xnntieu", NpgsqlDbType.Text).Value = xnntieu;
                cmd.Parameters.Add("sodokhac", NpgsqlDbType.Text).Value = sodokhac;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Varchar).Value = userid;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_khamthai (mabn,maql,makp,ngay,lanthu,para,ngaykinhc,tiensu,huyetap,timphoi,phune,khac,soluongthai,ngoithai,sodothai,timthai,trongluongthai,dukiensinh,xnmau,xnnuoctieu,sodokhac,ketluan,mabs,userid,ngayud) ";
                    sql += " values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy h24:mi'),:lanthu,:para,:ngaykinhc,:tiensu,:huyetap,:timphoi,:phune,:khac,:soluongthai,:ngoithai,:sodothai,:timthai,:trongluongthai,to_timestamp(:dukiensinh,'dd/mm/yyyy'),:xnmau,:xnntieu,:sodokhac,:ketluan,:mabs,:userid,now())";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("para", NpgsqlDbType.Text).Value = para;
                    cmd.Parameters.Add("ngaykinhc", NpgsqlDbType.Varchar).Value = ngaykinhc;
                    cmd.Parameters.Add("tiensu", NpgsqlDbType.Text).Value = tiensu;
                    cmd.Parameters.Add("huyetap", NpgsqlDbType.Text).Value = huyetap;
                    cmd.Parameters.Add("timphoi", NpgsqlDbType.Text).Value = timphoi;
                    cmd.Parameters.Add("phune", NpgsqlDbType.Text).Value = phune;
                    cmd.Parameters.Add("khac", NpgsqlDbType.Text).Value = khac;
                    cmd.Parameters.Add("soluongthai", NpgsqlDbType.Text).Value = soluongthai;
                    cmd.Parameters.Add("ngoithai", NpgsqlDbType.Numeric).Value = ngoithai;
                    cmd.Parameters.Add("sodothai", NpgsqlDbType.Text).Value = sodothai;
                    cmd.Parameters.Add("timthai", NpgsqlDbType.Text).Value = timthai;
                    cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Varchar).Value = trongluongthai;
                    cmd.Parameters.Add("dukiensinh", NpgsqlDbType.Varchar).Value = dukiensinh;
                    cmd.Parameters.Add("xnmau", NpgsqlDbType.Text).Value = xnmau;
                    cmd.Parameters.Add("xnntieu", NpgsqlDbType.Text).Value = xnntieu;
                    cmd.Parameters.Add("sodokhac", NpgsqlDbType.Text).Value = sodokhac;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Varchar).Value = userid;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_khamthai");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }

        public bool upd_sieuam4d(string m_mabn, long l_maql, string makp, string ngay, int lanthu, string tucung, string phanphu, string sothai,
            string cudongthai, string duongkinh, string chieudaidui, string chieudaichan, string chuvidau, string chuvibung, string kcachnhancau, string chieudaimong, string kichthuoc,
            string khoangsang, string chieudaimui, string bohopso, string cautrucnao, string hexuong1, string hexuong2, string hexuong3, string thanhbung, string bangquang, string haithan, 
            string vitrirau, string dotruongthanhrau, string dodayrau, string mepbanhrau, string soluongnuocoi, string tuoithai_kcc, string tuoithai_sa, string dudoansinh, string trongluongthai, string nhanxet, 
            string longnguc,string haiphoi,string dodongtam, string tuidaday, string dopler, string dmron, string dmtucung_p, string dmtucung_t, string dmnao, string chisonao, string ketluan, string mabs, int userid,string nhiptim)
        {
            sql = "update " + user + ".ba_sieuam4d set makp=:makp, ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), lanthu=:lanthu, tucung=:tucung, phanphu=:phanphu, sothai=:sothai,";
            sql += " cudongthai=:cudongthai, duongkinh=:duongkinh, chieudaidui=:chieudaidui, chieudaichan=:chieudaichan, chuvidau=:chuvidau,chuvibung=:chuvibung, kcachnhancau=:kcachnhancau, chieudaimong=:chieudaimong, kichthuoc=:kichthuoc,";
            sql += " khoangsang=:khoangsang, chieudaimui=:chieudaimui, bohopso=:bohopso,cautrucnao=:cautrucnao, hexuong1=:hexuong1,hexuong2=:hexuong2,hexuong3=:hexuong3,thanhbungtruoc=:thanhbung,bangquang=:bangquang,haithan=:haithan,";
            sql += " vitrirau=:vitrirau, dotruongthanhrau=:dotruongthanhrau, dodayrau=:dodayrau, mepbanhrau=:mepbanhrau, soluongnuocoi=:soluongnuocoi, tuoithai_kcc=:tuoithai_kcc, tuoithai_sa=:tuoithai_sa, dudoansinh=:dudoansinh, trongluongthai=:trongluongthai,";
            sql += " nhanxet=:nhanxet,ketluan=:ketluan, mabs=:mabs, userid=:userid, ngayud=now(), dodongtam=:dodongtam, tuidaday=:tuidaday, dopler=:dopler, dmron=:dmron, dmtucung_p=:dmtucung_p, dmtucung_t=:dmtucung_t,dmnao=:dmnao,chisonao=:chisonao,longnguc=:longnguc, haiphoi=:haiphoi,nhiptim=:nhiptim where maql=:l_maql and mabn=:m_mabn";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                cmd.Parameters.Add("sothai", NpgsqlDbType.Text).Value = sothai;
                cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                cmd.Parameters.Add("chieudaidui", NpgsqlDbType.Text).Value = chieudaidui;
                cmd.Parameters.Add("chieudaichan", NpgsqlDbType.Text).Value = chieudaichan;
                cmd.Parameters.Add("chuvidau", NpgsqlDbType.Text).Value = chuvidau;
                cmd.Parameters.Add("chuvibung", NpgsqlDbType.Text).Value = chuvibung;
                cmd.Parameters.Add("kcachnhancau", NpgsqlDbType.Text).Value = kcachnhancau;
                cmd.Parameters.Add("chieudaimong", NpgsqlDbType.Text).Value = chieudaimong;
                cmd.Parameters.Add("kichthuoc", NpgsqlDbType.Text).Value = kichthuoc;
                cmd.Parameters.Add("khoangsang", NpgsqlDbType.Text).Value = khoangsang;
                cmd.Parameters.Add("chieudaimui", NpgsqlDbType.Text).Value = chieudaimui;
                cmd.Parameters.Add("bohopso", NpgsqlDbType.Text).Value = bohopso;
                cmd.Parameters.Add("cautrucnao", NpgsqlDbType.Text).Value = cautrucnao;
                cmd.Parameters.Add("hexuong1", NpgsqlDbType.Text).Value = hexuong1;
                cmd.Parameters.Add("hexuong2", NpgsqlDbType.Text).Value = hexuong2;
                cmd.Parameters.Add("hexuong3", NpgsqlDbType.Text).Value = hexuong3;
                cmd.Parameters.Add("thanhbung", NpgsqlDbType.Text).Value = thanhbung;
                cmd.Parameters.Add("bangquang", NpgsqlDbType.Text).Value = bangquang;
                cmd.Parameters.Add("haithan", NpgsqlDbType.Text).Value = haithan;
                cmd.Parameters.Add("vitrirau", NpgsqlDbType.Text).Value = vitrirau;
                cmd.Parameters.Add("dotruongthanhrau", NpgsqlDbType.Text).Value = dotruongthanhrau;
                cmd.Parameters.Add("dodayrau", NpgsqlDbType.Text).Value = dodayrau;
                cmd.Parameters.Add("mepbanhrau", NpgsqlDbType.Text).Value = mepbanhrau;
                cmd.Parameters.Add("soluongnuocoi", NpgsqlDbType.Text).Value = soluongnuocoi;
                cmd.Parameters.Add("tuoithai_kcc", NpgsqlDbType.Text).Value = tuoithai_kcc;
                cmd.Parameters.Add("tuoithai_sa", NpgsqlDbType.Text).Value = tuoithai_sa;
                cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Text).Value = trongluongthai;
                cmd.Parameters.Add("nhanxet", NpgsqlDbType.Text).Value = nhanxet;
                cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("dodongtam", NpgsqlDbType.Text).Value = dodongtam;
                cmd.Parameters.Add("tuidaday", NpgsqlDbType.Text).Value = tuidaday;
                cmd.Parameters.Add("dopler", NpgsqlDbType.Text).Value = dopler;
                cmd.Parameters.Add("dmron", NpgsqlDbType.Text).Value = dmron;
                cmd.Parameters.Add("dmtucung_p", NpgsqlDbType.Text).Value = dmtucung_p;
                cmd.Parameters.Add("dmtucung_t", NpgsqlDbType.Text).Value = dmtucung_t;
                cmd.Parameters.Add("dmnao", NpgsqlDbType.Text).Value = dmnao;
                cmd.Parameters.Add("chisonao", NpgsqlDbType.Text).Value = chisonao;
                cmd.Parameters.Add("longnguc", NpgsqlDbType.Text).Value = longnguc;
                cmd.Parameters.Add("haiphoi", NpgsqlDbType.Text).Value = haiphoi;
                cmd.Parameters.Add("nhiptim", NpgsqlDbType.Text).Value = nhiptim;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".ba_sieuam4d (mabn,maql,makp,ngay,lanthu,tucung,phanphu,sothai,cudongthai,duongkinh,chieudaidui,chieudaichan,chuvidau,chuvibung,kcachnhancau,chieudaimong,kichthuoc";
                    sql += ",khoangsang,chieudaimui,bohopso,cautrucnao,hexuong1,hexuong2,hexuong3,thanhbungtruoc,bangquang,haithan,vitrirau,dotruongthanhrau,dodayrau,mepbanhrau,soluongnuocoi,tuoithai_kcc,tuoithai_sa,dudoansinh,trongluongthai,nhanxet,ketluan,mabs,userid,ngayud,longnguc,haiphoi,dodongtam,tuidaday,dopler,dmron,dmtucung_p,dmtucung_t,dmnao,chisonao,nhiptim) ";
                    sql += " values(:m_mabn,:l_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:lanthu,:tucung,:phanphu,:sothai,:cudongthai,:duongkinh,:chieudaidui,:chieudaichan,:chuvidau,:chuvibung,:kcachnhancau,:chieudaimong,:kichthuoc";
                    sql += ",:khoangsang,:chieudaimui,:bohopso,:cautrucnao,:hexuong1,:hexuong2,:hexuong3,:thanhbung,:bangquang,:haithan,:vitrirau,:dotruongthanhrau,:dodayrau,:mepbanhrau,:soluongnuocoi,:tuoithai_kcc,:tuoithai_sa,:dudoansinh,:trongluongthai,:nhanxet,:ketluan,:mabs,:userid,now(),:longnguc,:haiphoi,:dodongtam,:tuidaday,:dopler,:dmron,:dmtucung_p,:dmtucung_t,:dmnao,:chisonao,:nhiptim)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("l_maql", NpgsqlDbType.Numeric).Value = l_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("lanthu", NpgsqlDbType.Numeric).Value = lanthu;
                    cmd.Parameters.Add("tucung", NpgsqlDbType.Text).Value = tucung;
                    cmd.Parameters.Add("phanphu", NpgsqlDbType.Text).Value = phanphu;
                    cmd.Parameters.Add("sothai", NpgsqlDbType.Text).Value = sothai;
                    cmd.Parameters.Add("cudongthai", NpgsqlDbType.Text).Value = cudongthai;
                    cmd.Parameters.Add("duongkinh", NpgsqlDbType.Text).Value = duongkinh;
                    cmd.Parameters.Add("chieudaidui", NpgsqlDbType.Text).Value = chieudaidui;
                    cmd.Parameters.Add("chieudaichan", NpgsqlDbType.Text).Value = chieudaichan;
                    cmd.Parameters.Add("chuvidau", NpgsqlDbType.Text).Value = chuvidau;
                    cmd.Parameters.Add("chuvibung", NpgsqlDbType.Text).Value = chuvibung;
                    cmd.Parameters.Add("kcachnhancau", NpgsqlDbType.Text).Value = kcachnhancau;
                    cmd.Parameters.Add("chieudaimong", NpgsqlDbType.Text).Value = chieudaimong;
                    cmd.Parameters.Add("kichthuoc", NpgsqlDbType.Text).Value = kichthuoc;
                    cmd.Parameters.Add("khoangsang", NpgsqlDbType.Text).Value = khoangsang;
                    cmd.Parameters.Add("chieudaimui", NpgsqlDbType.Text).Value = chieudaimui;
                    cmd.Parameters.Add("bohopso", NpgsqlDbType.Text).Value = bohopso;
                    cmd.Parameters.Add("cautrucnao", NpgsqlDbType.Text).Value = cautrucnao;
                    cmd.Parameters.Add("hexuong1", NpgsqlDbType.Text).Value = hexuong1;
                    cmd.Parameters.Add("hexuong2", NpgsqlDbType.Text).Value = hexuong2;
                    cmd.Parameters.Add("hexuong3", NpgsqlDbType.Text).Value = hexuong3;
                    cmd.Parameters.Add("thanhbung", NpgsqlDbType.Text).Value = thanhbung;
                    cmd.Parameters.Add("bangquang", NpgsqlDbType.Text).Value = bangquang;
                    cmd.Parameters.Add("haithan", NpgsqlDbType.Text).Value = haithan;
                    cmd.Parameters.Add("vitrirau", NpgsqlDbType.Text).Value = vitrirau;
                    cmd.Parameters.Add("dotruongthanhrau", NpgsqlDbType.Text).Value = dotruongthanhrau;
                    cmd.Parameters.Add("dodayrau", NpgsqlDbType.Text).Value = dodayrau;
                    cmd.Parameters.Add("mepbanhrau", NpgsqlDbType.Text).Value = mepbanhrau;
                    cmd.Parameters.Add("soluongnuocoi", NpgsqlDbType.Text).Value = soluongnuocoi;
                    cmd.Parameters.Add("tuoithai_kcc", NpgsqlDbType.Text).Value = tuoithai_kcc;
                    cmd.Parameters.Add("tuoithai_sa", NpgsqlDbType.Text).Value = tuoithai_sa;
                    cmd.Parameters.Add("dudoansinh", NpgsqlDbType.Text).Value = dudoansinh;
                    cmd.Parameters.Add("trongluongthai", NpgsqlDbType.Text).Value = trongluongthai;
                    cmd.Parameters.Add("nhanxet", NpgsqlDbType.Text).Value = nhanxet;
                    cmd.Parameters.Add("ketluan", NpgsqlDbType.Text).Value = ketluan;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("longnguc", NpgsqlDbType.Text).Value = longnguc;
                    cmd.Parameters.Add("haiphoi", NpgsqlDbType.Text).Value = haiphoi;
                    cmd.Parameters.Add("dodongtam", NpgsqlDbType.Text).Value = dodongtam;
                    cmd.Parameters.Add("tuidaday", NpgsqlDbType.Text).Value = tuidaday;
                    cmd.Parameters.Add("dopler", NpgsqlDbType.Text).Value = dopler;
                    cmd.Parameters.Add("dmron", NpgsqlDbType.Text).Value = dmron;
                    cmd.Parameters.Add("dmtucung_p", NpgsqlDbType.Text).Value = dmtucung_p;
                    cmd.Parameters.Add("dmtucung_t", NpgsqlDbType.Text).Value = dmtucung_t;
                    cmd.Parameters.Add("dmnao", NpgsqlDbType.Text).Value = dmnao;
                    cmd.Parameters.Add("chisonao", NpgsqlDbType.Text).Value = chisonao;
                    cmd.Parameters.Add("nhiptim", NpgsqlDbType.Text).Value = nhiptim;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "ba_sieuam4d");
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        #endregion
        #region Vienphi
        public bool upd_vienphill(long m_id,long m_sobienlai,string m_mabn, long m_maql, string makp, string ngay, int userid, string ghichu,decimal sotien,decimal mien, string mabs)
        {
            sql = "update " + user + ".v_vienphill set sobienlai=:m_sobienlai, mabn=:m_mabn,maql=:m_maql,makp=:makp,ngay=to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'), userid=:userid, ngayud=now(), ghichu=:ghichu,sotien=:sotien,mien=:mien,mabs=:mabs where id=:m_id";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_sobienlai", NpgsqlDbType.Numeric).Value = m_sobienlai;
                cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                cmd.Parameters.Add("ghichu", NpgsqlDbType.Text).Value = ghichu;
                cmd.Parameters.Add("sotien", NpgsqlDbType.Numeric).Value = sotien;
                cmd.Parameters.Add("mien", NpgsqlDbType.Numeric).Value = mien;
                cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".v_vienphill (id,sobienlai,mabn,maql,makp,ngay,userid,ngayud,ghichu,sotien,mien,mabs) values(:m_id,:m_sobienlai,:m_mabn,:m_maql,:makp,to_timestamp(:ngay,'dd/mm/yyyy hh24:mi'),:userid,now(),:ghichu,:sotien,:mien,:mabs)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_sobienlai", NpgsqlDbType.Numeric).Value = m_sobienlai;
                    cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                    cmd.Parameters.Add("m_maql", NpgsqlDbType.Numeric).Value = m_maql;
                    cmd.Parameters.Add("makp", NpgsqlDbType.Varchar).Value = makp;
                    cmd.Parameters.Add("ngay", NpgsqlDbType.Varchar).Value = ngay;
                    cmd.Parameters.Add("userid", NpgsqlDbType.Numeric).Value = userid;
                    cmd.Parameters.Add("ghichu", NpgsqlDbType.Text).Value = ghichu;
                    cmd.Parameters.Add("sotien", NpgsqlDbType.Numeric).Value = sotien;
                    cmd.Parameters.Add("mien", NpgsqlDbType.Numeric).Value = mien;
                    cmd.Parameters.Add("mabs", NpgsqlDbType.Varchar).Value = mabs;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "v_vienphill_" + m_mabn);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        public bool upd_vienphict(long m_id, int m_stt, int m_madoituong, long m_mavp, decimal m_soluong, decimal m_dongia, long m_idchidinh)
        {
            sql = "update " + user + ".v_vienphict set madoituong=:m_madoituong, mavp=:m_mavp,soluong=:m_soluong,dongia=:m_dongia,idchidinh=:m_idchidinh where id=:m_id and stt=:m_stt";
            con = new NpgsqlConnection(sConn);
            try
            {
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                cmd.Parameters.Add("m_madoituong", NpgsqlDbType.Numeric).Value = m_madoituong;
                cmd.Parameters.Add("m_mavp", NpgsqlDbType.Numeric).Value = m_mavp;
                cmd.Parameters.Add("m_soluong", NpgsqlDbType.Numeric).Value = m_soluong;
                cmd.Parameters.Add("m_dongia", NpgsqlDbType.Numeric).Value = m_dongia;
                cmd.Parameters.Add("m_idchidinh", NpgsqlDbType.Numeric).Value = m_idchidinh;
                int irec = cmd.ExecuteNonQuery();
                cmd.Dispose();
                if (irec == 0)
                {
                    sql = "insert into " + user + ".v_vienphict (id,stt,madoituong,mavp,soluong,dongia,idchidinh) values(:m_id,:m_stt,:m_madoituong,:m_mavp,:m_soluong,:m_dongia,:m_idchidinh)";
                    cmd = new NpgsqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("m_id", NpgsqlDbType.Numeric).Value = m_id;
                    cmd.Parameters.Add("m_stt", NpgsqlDbType.Numeric).Value = m_stt;
                    cmd.Parameters.Add("m_madoituong", NpgsqlDbType.Numeric).Value = m_madoituong;
                    cmd.Parameters.Add("m_mavp", NpgsqlDbType.Numeric).Value = m_mavp;
                    cmd.Parameters.Add("m_soluong", NpgsqlDbType.Numeric).Value = m_soluong;
                    cmd.Parameters.Add("m_dongia", NpgsqlDbType.Numeric).Value = m_dongia;
                    cmd.Parameters.Add("m_idchidinh", NpgsqlDbType.Numeric).Value = m_idchidinh;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose(); con.Close(); con.Dispose();
                }
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message, sComputer, "v_vienphict_" + m_id);
                return false;
            }
            finally
            {
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            return true;
        }
        #endregion
        public DataSet get_Timmabn(string m_hoten, string m_namsinh, string m_mabn)
        {
            try
            {
                m_hoten = Hoten_khongdau(m_hoten);
                sql = "select a.mabn,a.hoten,a.ngaysinh,a.namsinh,decode(a.phai,0,'Nam','Nu') as phai,diachi as diachi,b.tentt,c.tenquan,d.tenpxa";
                sql += " from " + user + ".btdbn a," + user + ".btdtt b," + user + ".btdquan c," + user + ".btdpxa d where a.matt=b.matt and a.maqu=c.maqu";
                sql += " and a.maphuongxa=d.maphuongxa ";
                if (m_hoten != "") sql += " and a.hotenkdau=:m_hoten";
                if (m_namsinh != "") sql += " and a.namsinh=:m_namsinh";
                if (m_mabn != "") sql += " and a.mabn<>:m_mabn";
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                if (m_hoten != "") cmd.Parameters.Add("m_hoten", NpgsqlDbType.Text).Value = m_hoten;
                if (m_namsinh != "") cmd.Parameters.Add("m_namsinh", NpgsqlDbType.Varchar).Value = m_namsinh;
                if (m_mabn != "") cmd.Parameters.Add("m_mabn", NpgsqlDbType.Varchar).Value = m_mabn;
                ada = new NpgsqlDataAdapter(cmd);
                ds = new DataSet();
                ada.Fill(ds);
                cmd.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                upd_error(ex.Message.ToString().Trim(), sComputer, "Timmabn");
            }
            return ds;
        }
    }
}
